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

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%'; 

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

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).

Note for LinqPad users: In C#, you can use dc.ExecuteQueryDynamic(sqlQueryStr, new object[] {... parameters ...} ).Dump(); and have the parameters as @p0 ... @pn inside the query string. Then you can write a static extension class and save it under My Extensions to be used in your LinqPad queries. The data context can be passed from the query window as DataContextBase dc via parameter, i.e. public static void SearchDialog(this DataContextBase dc, string searchString = "%") inside a public static extension class (in LinqPad 6, it is DataContext). Then you can rewrite the SQL query above as a string with parameters and invoke it from the C# context.

i had the same problem and i found that Microsoft has a systable that shows dependencies.

SELECT referenced_id , referenced_entity_name AS table_name , referenced_minor_name as column_name , is_all_columns_found FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT'); 

And this works with both Views and Triggers.

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.

-- Search in All Objects

SELECT OBJECT_NAME(OBJECT_ID), definition FROM sys.sql_modules WHERE definition LIKE '%' + 'ColumnName' + '%' GO 

-- Search in Stored Procedure Only

SELECT DISTINCT OBJECT_NAME(OBJECT_ID) FROM sys.Procedures WHERE object_definition(OBJECT_ID) LIKE '%' + 'ColumnName' + '%' GO 

SELECT * FROM sys.all_sql_modules WHERE definition LIKE '%CreatedDate%' 

You can use the below query to identify the values. But please keep in mind that this will not give you the results from encrypted stored procedure.

SELECT DISTINCT OBJECT_NAME(comments.id) OBJECT_NAME ,objects.type_desc FROM syscomments comments ,sys.objects objects WHERE comments.id = objects.object_id AND TEXT LIKE '%CreatedDate%' ORDER BY 1