Find all stored procedures that reference a specific column in some table

find all stored procedures that reference a column in table in mysql
find all stored procedures that reference a table
get column names from stored procedure
sql query to find table used in stored procedures in mysql
find all references to a table in sql server
find stored procedure that inserts into a specific table
how to find table references in stored procedures
how to find all tables used in a stored procedure in sql server

I have a value in a table that was changed unexpectedly. The column in question is CreatedDate: this is set when my item is created, but it's being changed by a stored procedure.

Could I write some type of SELECT statement to get all the procedure names that reference this column from my table?

One option is to create a script file.

Right click on the database -> Tasks -> Generate Scripts

Then you can select all the stored procedures and generate the script with all the sps. So you can find the reference from there.

Or

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'CreatedDate' + '%'
GO

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%'
GO

Source SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

SQL SERVER - Find Column Used in Stored , Can anyone tell me if there is a way to get the list of all the columns used in a stored procedure and also their table names? Reply. Nabeel Asif. Many years ago when I was still working on SQL 2000 I occasionally needed to find all stored procedures and views that referenced a certain table. After some research I found the system table syscomments. This table has a column “text” that contains the “Actual text of the SQL definition statement.”

If you want to get stored procedures using specific column only, you can use try this query:

SELECT DISTINCT Name
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%CreatedDate%';

If you want to get stored procedures using specific column of table, you can use below query :

SELECT DISTINCT Name 
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%tbl_name%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%CreatedDate%';

https://social.msdn.microsoft.com/Forums/sqlserver, Scenario: A developer wants to drop a column from a table Find Stored Procedure Related to Table in Database – Search in All Stored Procedure Reference: Pinal Dave (https://blog.sqlauthority.com) only allow a certain role or privilege to see the data in that column even if its in a stored procedure? Using showplan_All as outlined in SQL Table and column Parser for stored procedures doesnt work for me, because this is a shared dev db. using a Sp from master db scanning system text as described is not feasible because I dont have access to the master db.

You can use ApexSQL Search, it's a free SSMS and Visual Studio add-in and it can list all objects that reference a specific table column. It can also find data stored in tables and views. You can easily filter the results to show a specific database object type that references the column

Disclaimer: I work for ApexSQL as a Support Engineer

How To Find the Objects and Columns a Stored Procedure or View , Find what objects are referenced in stored procedures or views. actually use the table or even the specific column in the table that you are changing. In my last post I covered finding objects that reference another object using the dmf, database that use several different objects to see how this works. SQL SERVER - Find Column Used in Stored Procedure - Search Stored Procedure for Column Name. Place: Any Developer Shop Scenario: A developer wants to drop a column from a table Time: Any Day - usually right before developer wants to go home The developer rushes to the manager and following conversation begins: Developer: I want to drop a column from one of the tables. Manager:….

You can use the system views contained in information_schema to search in tables, views and (unencrypted) stored procedures with one script. I developed such a script some time ago because I needed to search for field names everywhere in the database.

The script below first lists the tables/views containing the column name you're searching for, and then the stored procedures source code where the column is found. It displays the result in one table distinguishing "BASE TABLE", "VIEW" and "PROCEDURE", and (optionally) the source code in a second table:

DECLARE @SearchFor nvarchar(max)='%CustomerID%' -- search for this string
DECLARE @SearchSP bit = 1 -- 1=search in SPs as well
DECLARE @DisplaySPSource bit = 1 -- 1=display SP source code

-- tables
if (@SearchSP=1) begin  
  (
  select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object], 
            t.table_type 
  from information_schema.columns c
  left join information_schema.Tables t on c.table_name=t.table_name
  where column_name like @SearchFor 
  union
  select '['+routine_Schema+'].['+routine_Name+']' [schema_object], 
         'PROCEDURE' as table_type from information_schema.routines
  where routine_definition like @SearchFor
        and routine_type='procedure'
  )
  order by table_type, schema_object
end else begin
  select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object], 
         t.table_type 
  from information_schema.columns c
  left join information_schema.Tables t on c.table_name=t.table_name
  where column_name like @SearchFor 
  order by c.table_Name, c.column_name
end     
-- stored procedure (source listing)
if (@SearchSP=1) begin      
    if (@DisplaySPSource=1) begin
      select '['+routine_Schema+'].['+routine_Name+']' [schema.sp], routine_definition 
      from information_schema.routines
      where routine_definition like @SearchFor
      and routine_type='procedure'
      order by routine_name
    end
end

If you run the query, use the "result as text" option - then you can use "find" to locate the search text in the result set (useful for long source code).

Note that you can set @DisplaySPSource to 0 if you just want to display the SP names, and if you're just looking for tables/views, but not for SPs, you can set @SearchSP to 0.

Example result (find CustomerID in the Northwind database, results displayed via LinqPad):

Note that I've verfied this script with a test view dbo.TestOrders and it found the CustomerID in this view even though c.* was used in the SELECT statement (referenced table Customers contains the CustomerIDand hence the view is showing this column).

How do I find all stored procedures or views that use a specific table , needed to find all stored procedures and views that referenced a certain table. This table has a column “text” that contains the “Actual text of the SQL This DMV returns any object that references the object you pass it. You can use ApexSQL Search, it's a free SSMS and Visual Studio add-in and it can list all objects that reference a specific table column. It can also find data stored in tables and views. You can easily filter the results to show a specific database object type that references the column Disclaimer: I work for ApexSQL as a Support Engineer

try this..

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%CreatedDate%'
GO

or you can generate a scripts of all procedures and search from there.

How to find the columns and tables used in a stored procedure (SQL , Also if a procedure references something that is user schema work, otherwise there could be several procedures with the same name in different for the first table the procedure references, all columns in the second table  Please remember, that text column in syscomments is varchar(255), so one big procedure can consist of many lines in syscomments, thus, the above selects will not find the procedure name if the table name, you are searching for, has been split into 2 text rows in syscomments.

Find objects used by specific stored procedure in SQL Server , Find objects used by specific stored procedure in SQL Server database. Bart Gawrych Table of Contents: Query; Columns; Rows; Sample Result Query below return all stored procedures and objects used by them. You can get it in a few minutes and enable your team make better use of your data. If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

Find stored procedures that reference a table column, We recently moved away from SQL Server replication and the database still has all of the rowguid columns and their associated indexes and  Expand Stored Procedures, right-click the procedure and then click View Dependencies. View the list of objects that depend on the procedure. View the list of objects on which the procedure depends. Click OK. Using Transact-SQL. To view the dependencies of a procedure in Query Editor. System Function: sys.dm_sql_referencing_entities

SQL Server – Find Column Used in Stored Procedure – Search , How to find column used in a stored procedure. Find Stored Procedure Related to Table in Database – Search in All Stored Procedure · Search Reference: Pinal Dave (http://blog.sqlauthority.com) It looks like an article with general knowledge and no specific orientation to any programming language  Earlier this week I wrote a blog about Find Column Used in Stored Procedure – Search Stored Procedure for Column Name. I received plenty of comments on the subject. One of the statements which I used in the story (Time: Any Day – usually right before developer wants to go home) was very much liked by many developers. I guess this is because we are all like the same. We often get more work

Comments
  • Have a look at the sys.all_sql_modules table. In particular the column titled definition
  • Possible duplicate : stackoverflow.com/questions/686247/…
  • Does this just find objects / procedures with a defined parameter that matches the WHERE clause, or would this also find references to MyTable.SomeColumn within the objects themselves?
  • Perfect! Works great for me. Thanks
  • This doesn't give just the column named FirstName in a specific SP, but in any SP. This is fine until you have the same column name in use in several tables, views,.... There is no tool I know of that can find every place a specific column is referenced.