Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers

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.

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;
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 yesterday
    
@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 yesterday
add comment

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.

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

Your Answer

 
discard

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.