In SQL Server, how can I find everywhere a column is referenced?

by

Warning: Even though this is a text-search method, the script I’m going to share has saved me lots and lots of hours. It searches inside:

  • scalar functions
  • table-valued functions
  • stored procedures
  • views
  • triggers

I needed to specify a collation to make it work for me.

SELECT
    sys.objects.object_id, 
    sys.schemas.name AS [Schema], 
    sys.objects.name AS Object_Name, 
    sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK) 
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id 
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
    sys.sql_modules.definition COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%{Column Name}%' ESCAPE '\'
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name

The output is like the following:

Output

Update: If you need to search for a certain table, SP, etc. you could use a more specialized query:

DECLARE @SCHEMA_NAME VARCHAR(100) = 'dbo';
DECLARE @OBJECT_NAME VARCHAR(100) = 'MY_OBJECT';

SELECT
    sys.objects.object_id,
    sys.schemas.name AS [Schema], 
    sys.objects.name AS Object_Name, 
    sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK) 
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id 
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
    (
           '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'
    )
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name

P.S.: Both queries search inside comments too.

More Related Contents:

  1. Export from SQL Server to Excel
  2. How to get cumulative sum
  3. How to check if a column exists in a SQL Server table
  4. SQL Server query to find all permissions/access for all users in a database
  5. Infinite loop CTE with OPTION (maxrecursion 0)
  6. How Can I Log and Find the Most Expensive Queries?
  7. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  8. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  9. How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
  10. INSERT INTO vs SELECT INTO
  11. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  12. Query times out when executed from web, but super-fast when executed from SSMS
  13. How to: Create trigger for auto update modified date with SQL Server 2008
  14. How to update primary key
  15. error when inserting into table having instead of trigger from entity data framework
  16. Case sensitive variables in SQL Server
  17. What happens to the primary key Id when it goes over the limit?
  18. SQL Recursive CTE: Finding objects linked by property
  19. Convert decimal time to hours and minutes
  20. SQL Server Configuration Manager cannot be found
  21. SQL Server recursive query
  22. How do I move a table into a schema in T-SQL
  23. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  24. How to find the boundaries of groups of contiguous sequential numbers?
  25. How to wait for 2 seconds?
  26. What are row, page and table locks? And when they are acquired?
  27. Error converting data types when importing from Excel to SQL Server 2008
  28. CHECKSUM and CHECKSUM_AGG: What’s the algorithm?
  29. SQL Server 2008 Spatial: find a point in polygon
  30. Implementing one-to-zero-or-one relation in SQL Server

Leave a Comment