CODEWITHSUNDEEP
sqlsql-servert-sql
seroth
seroth

Reputation: 647

How to query a column in a table in all databases

I wish to query a column in a table in every database that the table exists. So far I have the following but am not sure where to go from here:

DECLARE @tblName VARCHAR(5000) = 'myTable'

SELECT name
FROM sys.databases
WHERE CASE
    WHEN state_desc = 'ONLINE'
        THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].['+@tblName+']','U')
    END IS NOT NULL

So far I can return all databases with the table I am looking for but I want all the column data from a specific column in the specified table in every database on the SQL server. Thank you for your help in advance.

Upvotes: 2

Views: 435

Answers (3)

azim
azim

Reputation: 595

you could use something like this

Create #tempTable(col datatype, col1 datatyep.....)
;EXEC sp_msforeachdb 'USE [?]
                    IF DB_NAME() IN(SELECT name
                                FROM sys.databases
                                WHERE CASE
                                    WHEN state_desc = ''ONLINE''
                                        THEN OBJECT_ID(QUOTENAME(name) + ''.[dbo].[myTable]'',''U'')
                                    END IS NOT NULL)
                        INSERT INTO #tempTable
                        SELECT mycolumn from my table
                        '
SELECT * from #tempTable

Upvotes: 0

T I
T I

Reputation: 9943

sp_MSForEachDb can be buggy, skip databases and other weird stuff. There is an alternative procedure here that has a very similar usage.

Another way would be

DECLARE @tbl SYSNAME = 'sys.objects'
DECLARE @sql NVARCHAR(MAX)

SET @sql = CAST((SELECT 'IF EXISTS(SELECT 1 FROM ' + QUOTENAME(name) + '.' + @tbl + ')' + 
                        ' SELECT TOP 1 ''' + QUOTENAME(name) + ''' db, * FROM ' + QUOTENAME(name) + '.' + @tbl AS 'data()'
                FROM sys.databases
                WHERE database_id > 4
                FOR XML PATH('')) AS NVARCHAR(MAX))


EXEC (@sql)

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

sp_foreachdatabase makes a nice shortcut: http://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

Upvotes: 5

Related Questions