Identifying Object Dependencies

When looking to migrate, consolidate or deprovision parts of a SQL solution it’s key to understand the dependencies on the objects inside.

Identifying dependencies can be challenging and I wanted to demonstrate one way to approach this. We’ll start with some objects across a couple of databases:

USE DbOne
GO

CREATE TABLE dbo.RefTable (
	ID INT IDENTITY(1, 1) PRIMARY KEY
);
GO

CREATE VIEW dbo.RefDbOne
AS
	SELECT *
	FROM dbo.RefTable;
GO

CREATE PROC dbo.MaxRef AS
BEGIN
	EXEC sp_executesql N'
		SELECT MAX(ID)
		FROM dbo.RefTable;';
END
GO

USE DbTwo
GO

CREATE VIEW dbo.RefDbTwo
AS
	SELECT *
	FROM DbOne.dbo.RefTable
GO

CREATE PROC dbo.GetLastRef AS
BEGIN
	SELECT TOP (1) ID
	FROM DbOne.dbo.RefTable
	ORDER BY ID DESC;
END
GO

The simplest option to find references within a database can be to use the ‘View Dependencies’ option within Management Studio. This only searches within the same database so we see minimal results:

That’s fine in the same database but when approaching a scenario such as consolidation or migration for example, this doesn’t help with:

• Objects in other databases aren’t identified by this feature
• This isn’t available for some types of objects, for example linked servers
• References aren’t defined for dynamic SQL queries

To alleviate these issues this approach uses the system view sys.all_sql_modules which contains the definition for objects such as views and procedures. We’ll wrap a script around the view to search our databases. Here’s what it looks like:

DECLARE @SearchReference VARCHAR(50) = 'RefTable',
	@SearchCommand NVARCHAR(2000);

CREATE TABLE #References (
	DbName NVARCHAR(128),
	SchemaName NVARCHAR(128),
	ObjectName NVARCHAR(128),
	ObjectType NVARCHAR(120),
	Snip NVARCHAR(100)
);
	
SET @SearchCommand = '
	USE [?]
	INSERT INTO #References
	SELECT DbName = DB_NAME(),
		SchemaName = SCHEMA_NAME(o.schema_id),
		ObjectName = OBJECT_NAME(m.object_id),
		ObjectType = o.[type_desc],
		Snip = ''...'' + SUBSTRING(m.[definition], CHARINDEX(''' + @SearchReference + ''', m.[definition]) - 20, 50) + ''...''
	FROM sys.all_sql_modules m
		INNER JOIN sys.objects o ON m.object_id = o.object_id
	WHERE [definition] LIKE ''%' + @SearchReference + '%''
	';

EXEC sp_msforeachdb @command1 = @SearchCommand;

SELECT * FROM #References;

We’ve got a temp table to store the results and then make use of some dynamic SQL and the sp_msforeachdb procedure to iterate over our databases to search for the specific text – in this case our table name.

The results based on the objects we created earlier are below. You’ll see we also spit out a small snip of where the text has been identified for some context:

We can see how we’ve now been able to overcome the challenges outlined above:

• We’re now checking all databases for references so have a much broader view
• This approach can help identify other references such as linked servers as its simply searching for test
• References within dynamic SQL scripts can also be picked up, again due to the text match

That isn’t to say this approach is a complete one. It’s better but there are still situations which can trip it up:

Firstly, sp_msforeachdb is built in so this script is pretty portable, however it is effectively undocumented and unsupported. There is a better alternative in the First Responder Kit by way of Aaron Bertrand but will need to be deployed separately.

Next up, with this being a text based search, some matches may be inaccurate. For example it could pick up an object which is commented out, or a partial match on another object. More accurate results can be found in the system view sys.sql_expression_dependencies which tracks dependencies across databases and even servers. This would however still miss dynamic SQL references.

Wrap up

In this post we’ve looked at a method for searching across our databases to identify where an object may be used across our environment.

This approach has clear benefits over the built in Management Studio feature. It also has both upsides and downsides to the alternative approach of using sys.sql_expression_dependencies as noted above. As of yet I haven’t found a wholly accurate approach to this type of interrogation.

I hope this is at least a good starting point for your investigations, it’s certainly been very helpful for me recently. If you have any suggestions on improvements or alternatives to consider please drop a comment below!