Technical Article

Script to Search Through all Databases for a Table Name

,

Copy and paste code in AdventureWorks 2008 and change the string to the table name you want to search for.

Next Step: With time permitting I will improve this script to search for any particular string within the entire server, This will include searching for objects like views, stored procs etc, etc.

-- Lets create a temporary holder for the database names
DECLARE @database TABLE (dbName VARCHAR(60))
DECLARE @db_name VARCHAR(50)
DECLARE @sql VARCHAR(MAX)

-- lets select non system databases and insert into the holder. we are exluding master,tempDB,MSDB and  model
SELECT  @sql = 'SELECT [Name] FROM sys.databases WHERE database_id > 4'

INSERT INTO @database
EXEC (@sql)

-- we are going to use a cursor to loop through and concantenate database name, schema name and table name
DECLARE @final_holder TABLE (db_Name_Schema_Name_Table_Name VARCHAR(100))

DECLARE db_cursor CURSOR FOR

SELECT DbName FROM @database

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@fetch_status = 0

BEGIN

DECLARE @vars nvarchar(max)
SELECT @vars = 'select table_catalog +''.''+table_schema + ''.'' + table_name from ' +@db_Name+ '.INFORMATION_SCHEMA.TABLES where table_type = ''BASE TABLE'''

INSERT INTO @final_holder
EXEC (@vars)

FETCH NEXT FROM db_cursor INTO @db_name

END
CLOSE db_cursor
DEALLOCATE db_cursor

-- now lets select the table name that we are searching for.
SELECT db_Name_Schema_Name_Table_Name FROM 
 (
SELECT * FROM @final_holder
) t
WHERE db_Name_Schema_Name_Table_Name LIKE '%Sales.Cust%'

-- results for searching string 'Sales.Cust'
/*
AdventureWorks.Sales.Customer
AdventureWorks.Sales.CustomerAddress
SIBS.Sales.Customer
SIBS.Sales.CustomerAddress
*/

Rate

2.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

2.67 (3)

You rated this post out of 5. Change rating