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

ACDBA 416 Reputation points
2022-03-25T08:10:36.713+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-03-25T09:00:39.173+00:00

    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.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Olaf Helper 42,386 Reputation points
    2022-03-25T08:56:39.207+00:00

    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'
    
    0 comments No comments

  2. Naomi 7,361 Reputation points
    2022-03-25T14:07:25.993+00:00

    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.

    0 comments No comments

  3. Erland Sommarskog 103.5K Reputation points MVP
    2022-03-25T22:27:36.853+00:00

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

    0 comments No comments