CODEWITHSUNDEEP
sql-server-2005
Relativity
Relativity

Reputation: 6868

How to get list of all stored procs/functions/view which refer a table?

I have a table table1 in database db1

How I can get list of all stored procs/functions/view which refer this table ?

Upvotes: 0

Views: 1848

Answers (1)

gbn
gbn

Reputation: 432210

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%table1%'

INFORMATION_SCHEMA.xxx are not reliable because the relevant columns are nvarchar(4000) which means you may not find the table. sys.sql_modules.definition is nvarchar(max)

Note the comment for ROUTINES

Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL.

To ensure you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.

Upvotes: 3

Related Questions