CODEWITHSUNDEEP
sql-server
vdrmrt
vdrmrt

Reputation: 872

Query to get list of views that use a certain field in SQL Server

Does anybody know of a query from system tables or views to get a list of views that use a certain field in a SQL Server database?

Upvotes: 0

Views: 494

Answers (1)

gbn
gbn

Reputation: 432667

SELECT *
FROM
    sys.sql_modules m
    JOIN
    sys.views v ON m.object_id = v.object_id
WHERE
    m.definition LIKE '%MyTable%' --or '%MyField%'

INFORMATION_SCHEMA views and legacy syscomments are unreliable for large view definition (or any definition) because they have nvarchar(4000) fields. sys.sql_modules uses nvarchar(max).

They should not be used

sys.sql_expression_dependencies may be an alternative but is more complex to use.

Upvotes: 2

Related Questions