Object to Syntax Scan on bulk scale

Question

Object to Syntax Scan on bulk scale

spitfire15

SSC Rookie

Points: 45
More actions
January 4, 2017 at 6:57 am

#316585

I have about 250 tables that I need to do a full scan on objects (stored procedures, views) to see if these tables are referenced in any of them.
Does anyone know how to write a query like this? I just need the return results to display object type, schema and object name.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125562 More actions · Answer 1

This query should do what you request by joining three system views. It joins objects with tables using a LIKE expression.

sys.sql_modules: contains T-SQL text for object definitions like stored procedures, views, etc.

sys.objects: contains one row for each object

sys.tables: contains one row for each table

SELECT obj.type_desc AS object_type

, SCHEMA_NAME(obj.schema_id) AS object_scheme

, obj.name AS object_name

, tbl.name AS contains_table

FROM sys.sql_modules AS sqltext

JOIN sys.objects AS obj ON obj.object_id = sqltext.object_id

JOIN sys.tables AS tbl ON sqltext.definition LIKE '%[^a-z]' + tbl.NAME + '%[^a-z]'

ORDER BY object_type, object_scheme, object_name, contains_table;

You may also want to add a WHERE clause to filter table names matching a specific naming convention. For example, if the tables you're wanting to search on have the prefix 'Product', then add the following clause:

WHERE tbl.name LIKE 'Product%'

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Why not use the DMVs?

SELECT t.name AS TableName, referencing_schema_name AS ReferencingSchema, referencing_entity_name AS ReferencingObject

FROM sys.tables t

CROSS APPLY sys.dm_sql_referencing_entities(SCHEMA_NAME(t.schema_id) + '.' + t.name, 'object')

ORDER BY TableName

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spitfire15 SSC Rookie Points: 45 More actions · Answer 3

Thanks Eric. This did work, only thing is the runtime is really long... I even used object_id in my WHERE clause, but the text string searching of those tables in stored procs is whats resource intensive.

spitfire15 SSC Rookie Points: 45 More actions · Answer 4

@Gail,

This works and runs fast, but dm_sql_referencing doesn't provide the object type, which is critical.

What I'm doing is dropping tables in a database that haven't had a system or user scan in the last 180 days. But before I do that, I need to make sure these tables aren't referenced in any other stored procs or views. This is a reporting database, so can't break any SSRS reports.