Query to find procedure or table exists.if yes where does it belong

Question

Hi All,

How can we write a query to search a proc or table name in an instance and provide the output saying 'proc' or 'table' exists in these databases.

Thanks,
ACDBA

Answer 1

Hi @ACDBA
To find a table or a proc in the SQL across all databases you can use undocumented stored procedure sp_MSForEachDB.
Check this :

CREATE TABLE #temp_table ( type varchar(100), name varchar(100),database_name varchar(100))  
  
EXEC sp_msforeachdb 'USE [?]; INSERT INTO #temp_table select type_desc, name,''?''  from sys.procedures WHERE name = ''name_to_find'''  
EXEC sp_msforeachdb 'USE [?]; INSERT INTO #temp_table select type_desc, name,''?''  from sys.tables WHERE name = ''name_to_find'''  
  
SELECT * FROM #temp_table  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Not very clear, but you can use DMV like sys.tables / sys.objects to check if an object with a specific name exists, like

select *
from sys.tables
where name = 'TableName';

select *
from sys.procedures
where name = 'spName';

if exists(select * from sys.procedures where name = 'sp_who')
    print 'exists'
else
    print 'do not exists'

Answer 3

See these two samples in this blog post
https://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-get-information-about-all-databas#5
and adapt for your needs. Essentially you're simply using dynamic sql against sys.databases and query other system views such as sys.objects, for example.

Answer 4

In addition to the other posts, you may also be interested in my short story Where Is that Table Used? on my web site.