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

ACDBA 416 Reputation points

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.


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

    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,

    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

    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'
        print 'do not exists'
    0 comments No comments

  2. Naomi 7,361 Reputation points

    See these two samples in this blog post
    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

    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