List all database containing table and value

Question

in one of our SQL Servers we have several databases attached and most of them contain a little table, let's say dbo.DBType and that table will always contain 1 record with two columns, first column tells me if the DB is of type A or B (only A and B are allowed).

is there a way I can query the master db of SQL Server and retrieve a list of all databases of type A or type B ?

I know I could connect inside a loop to each of them and check the containewd DBType table but was thinking

Answer 1

I'm guessing on the first column - replace ColumnName below with the actual column name. Also it's always useful to specify which version of SQL Server you are using - I've assumed 2005.

DECLARE @DBType CHAR(1);
SET @DBType = 'A'; -- or 'B';

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

CREATE TABLE #x(DB SYSNAME);

SELECT @sql = @sql + 'IF EXISTS (SELECT 1 FROM '
  + QUOTENAME(name) + '.sys.tables WHERE name = ''DBType'')
  INSERT #x SELECT ''' + name + '''
    FROM ' + QUOTENAME(name) + '.dbo.DBType
    WHERE ColumnName = ''' + @DBType + ''';'
FROM sys.databases;

EXEC sp_executesql @sql;

SELECT DB FROM #x;

DROP TABLE #x;

Answer 2

There's no built-in solution to accomplish what you are looking for. But you could easily design a stored procedure in the master database to achieve your end results. Something like this:

use master
go

create procedure dbo.sp_dbtypes
as
    set nocount on;

    declare @database_name sysname

    create table #dbtypes
    (
        database_name sysname not null,
        dbtype char(1) not null
    )

    declare db_cursor cursor for
    select
        name
    from sys.databases

    open db_cursor

    fetch next from db_cursor
    into @database_name

    while @@fetch_status = 0
        begin
            insert into #dbtypes
            (
                database_name,
                dbtype
            )
            exec
            (
                'if exists 
                ( 
                    select * 
                    from ' + @database_name + '.sys.tables
                    where name = ''dbtype'' 
                )
                    begin
                        select ''' + @database_name + ''', db_type
                        from ' + @database_name + '.dbo.DBType
                    end'
            )

            fetch next from db_cursor
            into @database_name
        end

    close db_cursor
    deallocate db_cursor

    select *
    from #dbtypes

go

Then to call this stored procedure, you'd simply do this:

exec master.dbo.sp_dbtypes

Obviously, you'll need to mesh this stored procedure into your environment but it should give you of a good idea to create the procedure. Outside of a cursor, I can't think of any other way.