Finding Table Names not referenced by TSQL Code

A question on Stack Overflow titled TSQL script to find tables not being used by stored procedures, views, functions, etc? reminded me of a recent post on searching the TSQL source code catalog view of MS SQL Server.

This query would list all occurrences of all table names in T-SQL code, regardless whether the table name really refers to that table, or is just a substring of whatever identifier:

select t.name, sys.objects.name foundin, sys.objects.type_desc
from sys.objects t
left outer join
	sys.sql_modules
	inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id
on sys.sql_modules.definition like '%' + t.name + '%'
where t.type = 'U'
order by t.name, type_desc, foundin

To list unreferenced table names, we add an IS NULL condition on the modules/objects:

select t.name, sys.objects.name foundin, sys.objects.type_desc
from sys.objects t
left outer join
	sys.sql_modules
	inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id
on sys.sql_modules.definition like '%' + t.name + '%'
where t.type = 'U'
and sys.objects.name is null
order by t.name, type_desc, foundin

Doing the same check on views is a bit trickier, since the view definition contains the view name itself in the code. Therefore the OUTER JOIN condition needs to be adjusted to exclude the view’s own source code from the match:

select t.name, sys.objects.name foundin, sys.objects.type_desc
from sys.objects t
left outer join
	sys.sql_modules
	inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id
on sys.sql_modules.definition like '%' + t.name + '%'
	and sys.objects.name != t.name
where t.type = 'V'
order by t.name, type_desc, foundin
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: