Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 10 down vote accepted

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.

SET @DBType = 'A'; -- or 'B';

SET @sql = N'';


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;


share|improve this answer
+1 Nice solution! – Thomas Stringer Apr 21 '12 at 23:38
Thanks Aaron this has done the job! – Davide Piras Apr 22 '12 at 11:10
One change I would suggest, replace ''' + name + ''' with ' + QUOTENAME(name,'''') + ' this will protect you in the event someone is weird enough to put a ' in their database name. – Scott Chamberlain Apr 22 '14 at 16:11
@Scott I guess, REPLACE() would be cleaner, but if people are putting ' in their database names, they kind of get what they deserve. :-) – Aaron Bertrand Apr 22 '14 at 16:14

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

create procedure dbo.sp_dbtypes
    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
    from sys.databases

    open db_cursor

    fetch next from db_cursor
    into @database_name

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

            fetch next from db_cursor
            into @database_name

    close db_cursor
    deallocate db_cursor

    select *
    from #dbtypes


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.

share|improve this answer
Mine didn't use a cursor. Well, not an explicit one, anyway. :-) – Aaron Bertrand Apr 21 '12 at 22:50

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.