I know…there has been a lot written about this subject. What I’m trying to do here is to summarize first the methods I use to find dependencies in SQL Server, and second to tell you why I don’t use other methods. There are third party tools out there, and some are very good at finding/displaying dependencies, but in this post I will concentrate on methods available using SQL Server and SSMS only.
What do I currently use?
I often have a need to answer the questions “what SQL objects use table X?”, or “What objects are referenced by stored procedure Y?” I routinely use 3 queries to answer these questions, so if you just want to find dependencies just grab this code and go (and don’t bother reading the rest!).
-- Search for dependencies -- Find all objects which reference the "Company" table -- note, need the schema name in the function call SELECT referencing_schema_name +'.'+ referencing_entity_name AS ReferencedEntityName, referencing_class_desc AS ReferencingEntityDescription FROM sys.dm_sql_referencing_entities ('dbo.Company', 'OBJECT'); -- Find all objects which are referenced by "usp_GetCompanyName" stored procedure -- note, need the schema name in the function call SELECT referenced_schema_name +'.'+ referenced_entity_name AS ReferencedEntityName, referenced_minor_name AS ReferencedMinorName FROM sys.dm_sql_referenced_entities ('dbo.usp_GetCompanyName', 'OBJECT'); -- do a "wildcard" search on all code objects -- note: if your table name contains underscores it may alter the results! SELECT SchemaName = s.name, ObjectName = o.name, o.type_desc FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE m.definition LIKE '%T_COMPANY%' ; --end of script
Some quick notes on the 3 queries above:
- I use the first query when I want to find all of the code objects that reference a given table. Include the schema name in the function call or you will not receive any results.
- I use the second query when I want to see all of the SQL objects referenced by a given stored procedure or function. When I say “All”, I mean “All”; every table, column, etc. I don’t use this query much.
- I use the last query when I want to search all of the SQL object code for any string. This uses the sys.sql_modules view, a replacement for sys.syscomments. Please note that it will search through comments, etc., so there will be “false positives” returned. Also note that if you use underscores in your table names (like the example), the underscore is a “wildcard” character that means “any single character”. So in the example above, searching for %T_COMPANY% really means “search for any number of characters followed by a T followed by any single character followed by COMPANY followed by any number of characters”. Keep this in mind because it may give you additional results that are NOT what you are looking for.
Now for the more difficult question: why don’t I use any of the other methods? Here they are, in no particular order, with the reason why I don’t use each:
- SSMS – View Dependencies: This option has been available in SSMS since, well, since SSMS has been in existence, and it has NEVER given me good results. I don’t know why, but Microsoft can’t seem to get it right. I always find dependencies later that were not listed using this option. So please, don’t use it.
- Stored procedure sp_depends: This proc usually gives me good results, but it doesn’t include triggers. It has also been deprecated by Microsoft, so it may not exist much longer.
- Query using INFORMATION_SCHEMA.ROUTINES: I thought that this was going to be my answer when it came out a few releases ago, especially for “wildcard” searches of all of the SQL object code. However, I soon discovered that only 4000 characters of each routine are available using this view, so my searches often did not find a result if the procedure was longer than 4000 characters.
- Query using sys.syscomments: again, deprecated.