CODEWITHSUNDEEP
sqldatabaseobject
poshan
poshan

Reputation: 3287

How to write a script to find databases that are used in SQL server database objects like storedproc and views?

One of our databases (Database A) in SQL server 2012 is retiring soon. I am in a process to analyze all Views and StoredProcs in Database B that uses Database A as a data source. How do I write a query to retrieve this information. Thanks

Upvotes: 1

Views: 290

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I use the following to get objects and their dependencies in SQL Server 2008:

select *
from (select referenced_database_name, o.name as ViewName, ed.referenced_entity_name as TableName
      from sys.sql_expression_dependencies ed join
           sys.objects o
           on ed.referencing_id = o.object_id
      where referenced_database_name is not null
     ) vdep

You can add a filter to filter out databases you are or are not interested in:

where referenced_database_name = 'A'

Upvotes: 2

Darren Kopp
Darren Kopp

Reputation: 77627

A very simple way would be to just query the procedure text

select p.name 
from sys.procedures p 
    inner join sys.syscomments c ON (p.object_id = c.id)
where c.text like '%database.%' or c.text like '%[database].%'

The same query can be done on sys.views as well. You may or may not get very good results depending on the database name and the naive algorithm I have given you.

I'm not sure if sp_depends works on databases or not, but I'm guessing there's dependencies you could query for the database as well, I just don't know how.

Upvotes: 0

Related Questions