Sql-server – Find a specific table and column value across all databases

sql serversystem-databasessystem-tables

This is a question about logic that could help me understand more how to iterate into my DBs.

On my server I have several AdventureWorks* databases:

enter image description here

I have a query that cold help me iterate through all my databases, AdventureWorks* and not:

CREATE TABLE #LIst_DB (name nvarchar(128))

INSERT INTO #LIst_DB 

select name from  sys.databases
WHERE database_id > 4
AND state = 0;

select * from #LIst_DB

DROP TABLE #LIst_DB

For each AdventureWorks* database I want to list AWBuildVersion next to each DB:

enter image description here

At this point I'm puzzled because there are 2 problems I don't know how to solve:

  1. How to run a select [Database Version] from AWBuildVersion for each database?
  2. What to do if a database has no AWBuildVersion table?

Here I found this query:

DECLARE @SQL NVARCHAR(max)

SET @SQL = stuff((
            SELECT '
UNION
SELECT ' + quotename(NAME, '''') + ' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name
FROM ' + quotename(NAME) + '.sys.tables WHERE NAME =  @TableName '
            FROM sys.databases
            ORDER BY NAME
            FOR XML PATH('')
                ,type
            ).value('.', 'nvarchar(max)'), 1, 8, '')

--PRINT @SQL;

EXECUTE sp_executeSQL @SQL
    ,N'@TableName varchar(30)'
    ,@TableName = 'AWBuildVersion'

That brings me close to my goal but it shows me the table name AWBuildVersion while I need the column Database Version = 11.0.2100.60

enter image description here

Best Answer

Comments inline:

-- The table to find
DECLARE @find nvarchar(257) = N'dbo.AWBuildVersion';

-- Holds results
DECLARE @results table 
(
    [Db_Name] sysname PRIMARY KEY,
    [Database Version] nvarchar(25) NOT NULL
);

-- Current database   
DECLARE @db sysname;

-- AdventureWorks databases cursor
DECLARE dbs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT D.[name] 
FROM sys.databases AS D
WHERE 
    D.[name] LIKE N'AdventureWorks%'
    AND D.state_desc = N'ONLINE'
    AND DATABASEPROPERTYEX(D.[name], 'Collation') IS NOT NULL
ORDER BY
    D.[name];

OPEN dbs;

WHILE 1 = 1
BEGIN
    -- Next database
    FETCH dbs INTO @db;
    IF @@FETCH_STATUS = -1 BREAK;

    -- Find [Database Version] if @find table exists
    INSERT @results ([Db_Name], [Database Version])
    EXECUTE sys.sp_executesql
        N'
            -- Construct 3-part object name
            DECLARE @object nvarchar(386) = 
                QUOTENAME(@db) + N''.'' +
                ISNULL(QUOTENAME(PARSENAME(@find, 2)), '''') + N''.'' +
                QUOTENAME(PARSENAME(@find, 1));

            -- Query to find [Database Version]
            DECLARE @sql nvarchar(max) = N''
                SELECT 
                    Db_Name = '''''' + @db + '''''',
                    [Database Version] 
                FROM '' + @object;

            IF OBJECT_ID(@object, N''U'') IS NOT NULL
                EXECUTE (@sql)',
        N'@db sysname, @find nvarchar(257)',
        @db = @db, @find = @find;
END;

CLOSE dbs; DEALLOCATE dbs;

SELECT
    R.[Db_Name],
    [Table_Name] = @find,
    R.[Database Version]
FROM @results AS R
ORDER BY
    R.[Db_Name];
Related Question