Script to Search Through all Databases for a Table Name

  • Comments posted to this topic are about the item Script to Search Through all Databases for a Table Name

  • A similar result may be achieved using the undocumented stored procedure sp_MSforeachdb:

    EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name from [?].INFORMATION_SCHEMA.TABLES where table_type = ''BASE TABLE'' AND TABLE_NAME LIKE ''%Sales.Cust%'''

  • Microsoft included a stored procedure with SQL Server 2000 called "sp_MSObjSearch" that did exactly what you're proposing to do. It's parameterized so that you can choose what type of object to search for.

    If you have an instance of SQL 2000 available, you can get view the code by executing sp_helptext sp_MSObjSearch.

    This procedure is incredibly helpful, and I'm mystified as to why MS stopped including it in the basic server installation. I've seen the procedure deployed successfully in SQL 2005 and SQL 2008 instances.

  • Thanks very much but I dont have sql 2000. I am using 2005 and 2008.

  • Would this not achieve the same thing?


    SELECT @SQL = STUFF(sqlCode.value('.', 'varchar(max)'),1,10,'') + ';'

    FROM (SELECT 'UNION ALL SELECT table_catalog + '+CHAR(39)+'.'+CHAR(39)+' + table_name ' + CHAR(13) + CHAR(10) +


    'WHERE table_type = ' + CHAR(39) + 'BASE TABLE' + CHAR(39) + ' AND table_name LIKE ' +

    CHAR(39) + @SEARCH_STRING + CHAR(39) + CHAR(13) + CHAR(10)

    FROM sys.databases

    WHERE database_id > 4

    FOR XML PATH(''), TYPE) a(sqlCode);


    EXECUTE sp_executesql @SQL;

    Forever trying to learn
    My blog -
    For better, quicker answers on T-SQL questions, click on the following...
    For better, quicker answers on SQL Server performance related questions, click on the following...

  • I really love a FREE tool - SQL Search from RED Gate -

    It seaches pretty much any DB subject, table, view, store proc, columns, etc., very fast. Thought you maybe interested.

  • We just made a similar script but focusing on the columns and not using the schemas (as they don't use them in here).

    But because we're not dbas and we don't have access to all databases, our query at sys.databases had a couple of extra filters to avoid errors.

    AND state_desc = 'ONLINE'

    AND HAS_DBACCESS (name) = 1

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks for the script.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply