Menu
Finding Things in SQL That Reference a Table
By Lori Brown | Dependencies
Have you ever been asked to make changes to a table that is used by other things/processes? One of the challenges is to figure out what will be impacted by the change. This way you can know what to check and test so that the change is rolled out with success.
One good place to look is in the dmf, sys.dm_sql_referencing_entities (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-sql-referencing-entities-transact-sql?view=sql-server-2017)
USE WideWorldImporters
GO
SELECT *
FROM sys.dm_sql_referencing_entities (‘Sales.Orders’, ‘OBJECT’);
GO
As you can see it returns a result set of the objects which of course means you can join sys.objects to it to get a bit more information.
USE WideWorldImporters
GO
SELECT *
INTO #temprefentities
FROM sys.dm_sql_referencing_entities (‘Sales.Orders’, ‘OBJECT’)
SELECT e.*, o.type_desc, o.create_date, o.modify_date
FROM #temprefentities e
JOIN sys.objects o ON (o.object_id = e.referencing_id)
DROP TABLE #temprefentities
GO
Another place to look is in sys.sql_modules (https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/view-the-definition-of-a-stored-procedure?view=sql-server-2017)
I use sys.sql_modules to check for references to strings in stored procedures. You might think that this would not be necessary since you can find that in sys.dm_sql_referencing_entities for those. However, what if you do a lot of stuff in stored procedures that uses dynamic SQL to create objects or other stored procs on the fly? sys.dm_sql_referencing_entities might miss those. For example, when I look in sys.sql_modules for all occurrences of Sales.Orders I find more.
USE WideWorldImporters
GO
SELECT *
FROM sys.sql_modules
WHERE definition LIKE ‘%Sales.Orders%’
GO
It turns out that the stored procedures, [Application].[Configuration_EnableInMemory] & [DataLoadSimulation].[Configuration_ApplyDataLoadSimulationProcedures] also use Sales.Orders but in dynamic SQL strings. Since I have several clients who use stored procs to create tables and jobs, I have learned to hedge my bets on finding obscure stuff like this.
You also need to check to see if any jobs use the object you are working on. I have a job that uses this query in its job step.
You can open each job searching for the Sales.Orders string or you can run the query below. At some places where there are hundreds of jobs, looking through each one would be impossible.
SELECT j.name AS JobName, js.step_id, js.step_name, js.command, j.enabled
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps js ON (js.job_id = j.job_id)
WHERE js.command LIKE ‘%Sales.Orders%’
I can also envision that if you are wanting to find all things that use a specific object, another place to look would be in any SSIS packages. Doing this requires that you parse package XML which can be difficult but will save you hours of searching. I found a TechNet post on doing this exact thing so I will just leave this here for your enjoyment.
If you can think of other interesting ways you have needed to find strings or object references, please let me know about how you did it. I know there must be many other ways to get a list of referencing objects. I have already written about sp_help here. https://www.sqlrx.com/using-sp_help-to-find-object-dependencies/
In my examples I have used the Wide World Importers database. This sample database replaces the old AdventureWorks databases. You can download the Wide World Importers OLTP and Data Warehouse databases here:
https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
Find out more about them here: https://docs.microsoft.com/en-us/sql/samples/wide-world-importers-what-is?view=sql-server-2017
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!