CODEWITHSUNDEEP
sql-server
Sneha ulal
Sneha ulal

Reputation: 19

List of databases have objects (like views or stored procs?) that reference a server

Is there any script to find out the list of database object that reference to a particular server? I am doing investigation. I would really appreciate your help!

Thanks

Upvotes: 0

Views: 31

Answers (1)

Greg
Greg

Reputation: 4055

You want to search all objects for a give text string, correct? sys.sql_modules is the DMV you want to use, it searches procs, functions, views, triggers, etc. Try the below, with your modified WHERE clause, it will work for SQL 2008+:

select sm.object_id
    , OBJECT_NAME(sm.object_id) as object_name
    , o.type
    , o.type_desc
    , sm.definition
FROM sys.sql_modules sm
INNER JOIN sys.objects o ON sm.object_id = o.object_id
WHERE sm.definition like '%somestring%'
ORDER BY 2

Upvotes: 1

Related Questions