CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Art F
Art F

Reputation: 4202

Search databases for table with values

I am using SQL Server 2008. My question is, is it possible, given a table name, to construct a query that returns a list of databases that contain that table WITH values (meaning it is not empty)?

For example, I have a table called tbl_Name. I have 100 databases, and in 90 of them tbl_Name is empty. Can I get a list of the 10 databases where tbl_Name has values?

Upvotes: 1

Views: 69

Answers (2)

Hart CO
Hart CO

Reputation: 34774

You can do this with a cursor, and the stored procedure sp_msforeachdb.

sp_msforeachdb is, as the name suggests, a proc that runs something for each database. You can use this to list all db's with a given table name: 

sp_msforeachdb 'SELECT "?" AS db from [?].sys.tables WHERE name = ''tbl_Name'''

Inserting those records into a temp table makes it easy to iterate over in a cursor:

DROP TABLE #db_List
DROP TABLE #Not_Empty
GO
CREATE TABLE #db_List (db VARCHAR(MAX))
CREATE TABLE #Not_Empty (db VARCHAR(MAX))
GO
sp_msforeachdb 'INSERT INTO #db_List SELECT "?" AS db from [?].sys.tables WHERE name = ''tbl_Name'''
GO

SET NOCOUNT ON
DECLARE @Iterator varchar(MAX)
       ,@strSQL varchar(MAX) 
DECLARE xyz CURSOR
FOR
    --Select stuff to iterate over
    SELECT *
    FROM #db_List
OPEN xyz 
FETCH NEXT FROM xyz 
INTO @Iterator 
WHILE @@FETCH_STATUS = 0
BEGIN
--Do stuff
SET @strSQL = 'INSERT INTO #Not_Empty              
               SELECT '''+@Iterator+'''
               WHERE EXISTS (SELECT TOP 1 * FROM '+@Iterator+'..tbl_Name)
               '
Exec (@strSQL)
    FETCH NEXT FROM xyz 
    INTO @Iterator 
END
CLOSE xyz 
DEALLOCATE xyz 
GO
SET NOCOUNT OFF

Upvotes: 1

T McKeown
T McKeown

Reputation: 12857

Quick summary:

Start in master DB select from sys.databases, get all the DB's, cursor thru each one (USE the DB). Use EXEC( sqlStatement ) to perform your command etc...

Now that you're in the DB (Each DB has a table named sys.objects, so your goal from the above paragraph is to simply issue a command like EXEC(USE dbName) ) list through the list of tables, select from sys.objects, look for object types of 'U' (user table). Get the name, construct your SELECT * FROM INTO #yourTemp WHERE blah blah blah insert your results in a #temp table you created at the very beginning.. Issue the SQL via EXEC()

Upvotes: 0

Related Questions