Select all databases that have a certain table and a certain column

Question

I need to create a query that is executed on all databases of my SQL server instance. An additional constraint is, that the query should only be executed on databases that contain a special table with a special column. Background is that in some databases the special table does (not) have the special column.

Based on this solution, what I have until now is a query that executes only on databases that contain a certain table.

SELECT *
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
    AND HAS_DBACCESS(name) = 1
    AND state_desc = 'ONLINE'
    AND  CASE WHEN state_desc = 'ONLINE' 
              THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[CERTAIN_TABLE]', 'U')
         END IS NOT NULL

However, what is still missing is a constraint that the query should only select databases where the table CERTAIN_TABLE has a specific column. How can this be achieved?

Have you considering inspecting the values in `INFORMATION_SCHEMA.COLUMNS` and checking to see if a row with the appropriate values for `TABLE_NAME` and `COLUMN_NAME` exists? — Thom A, Aug 14 '19 at 14:14
but how can I join from the `sys.databases` table to the `INFORMATION_SCHEMA.COLUMNS` from one specific database? That's my missing link. — beta, Aug 14 '19 at 14:49

score 1 · Answer 1 · answered Aug 14 '19 at 14:34

You are going to need either some looping or dynamic sql for this. I really dislike loops so here is how you could do this with dynamic sql.

declare @TableName sysname = 'CERTAIN_TABLE'
    , @ColumnName sysname = 'CERTAIN_COLUMN'

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'select DatabaseName = ''' + db.name + ''' from ' + QUOTENAME(db.name) + '.sys.tables t join ' + QUOTENAME(db.name) + '.sys.columns c on c.object_id = t.object_id where t.name = ''' + QUOTENAME(@TableName) + ''' and c.name = ''' + QUOTENAME(@ColumnName) + '''' + char(10) + 'UNION ALL '
from sys.databases db
where db.state_desc = 'ONLINE'
order by db.name

select @SQL = substring(@SQL, 0, len(@SQL) - 9)

select @SQL 

--uncomment the line below when you are comfortable the query generated is correct
--exec sp_executesql @SQL

I recommend `'...N''' + QUOTENAME(db.name,'''') + '''...'` instead of `'...''' + db.name + '''...'` here; just incase the OP has some silly databases with a single quote or character outside of the ASCII ranges. — Thom A, Aug 14 '19 at 14:52

Icculus018 · Accepted Answer · 2019-08-14T15:10:33.520

1

When i want to loop through all databases, i do a loop like the following. Its easy to follow:

DECLARE @dbs TABLE ( dbName NVARCHAR(100) )
DECLARE @results TABLE ( resultName NVARCHAR(100) )

INSERT INTO @dbs
SELECT name FROM sys.databases

DECLARE @current NVARCHAR(100)

WHILE (SELECT COUNT(*) FROM @dbs) > 0
BEGIN
    SET @current = (SELECT TOP 1 dbName FROM @dbs)

    INSERT INTO @results
    EXEC
    (
        'IF EXISTS(SELECT 1 FROM "' + @current + '".INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = ''Target_Table_Name'' AND COLUMN_NAME = ''Target_Column_Name'')
        BEGIN
            --table and column exists, execute query here
            SELECT ''' + @current + '''
        END'
    )

    DELETE FROM @dbs
    WHERE dbName = @current
END

SELECT * FROM @results

edited Aug 14 '19 at 15:10

answered Aug 14 '19 at 15:01

Icculus018

1,018
11
19

I like this solution, but just one more question: How can I get the results of the separate `SELECT` queries into a single result set? Somehow with `UNION ALL`, but not sure how/where to apply in your solution. – beta Aug 14 '19 at 15:05
Edits to show how to put into one result set applied. – Icculus018 Aug 14 '19 at 15:06
Thanks! At one point in the loop (it work the first few iterations), I get `Column name or number of supplied values does not match table definition.`. Will have to debug that on my own probably. – beta Aug 14 '19 at 15:18

Select all databases that have a certain table and a certain column

2 Answers2