CODEWITHSUNDEEP
sqlsql-servert-sql
EJoshuaS - Stand with Ukraine
EJoshuaS - Stand with Ukraine

Reputation: 12181

T-SQL to get a list of all of the databases that have certain tables

I'm trying to get a list of all of the databases that contain two separate tables with specific names (in this case, 'MasterSchedules', 'Users').

The first thing I tried was to do this with a cursor:

DECLARE dbCursor CURSOR FOR 
     SELECT name 
     FROM sys.databases 
     WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

DECLARE @dbname varchar(max)

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @stmt nvarchar(max)
    SET @stmt = 'USE ' + @dbname + ';';

    EXECUTE sp_executesql @stmt

    SELECT TABLE_CATALOG, TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' 
      AND TABLE_NAME in ('MasterSchedules', 'Users')
    ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME

    FETCH NEXT FROM dbCursor INTO @dbname
END

CLOSE dbCursor;
DEALLOCATE dbCursor;

However, all this does is get the same data over and over again.

---------------------------------------
|   | TABLE_CATALOG | TABLE_NAME      |
|--------------------------------------
| 1 | QA_CTLG       | MasterSchedules |
---------------------------------------

---------------------------------------
|   | TABLE_CATALOG | TABLE_NAME      |
|--------------------------------------
| 1 | QA_CTLG       | MasterSchedules |
---------------------------------------

---------------------------------------
|   | TABLE_CATALOG | TABLE_NAME      |
|--------------------------------------
| 1 | QA_CTLG       | MasterSchedules |
---------------------------------------

...

I've narrowed it down to the fact that EXECUTE sp_executesql... apparently has no effect. Does anyone know why that is or how to fix it?

For the record, yes, I do realize that this will show databases that have at least one of those tables, not the ones that have both.

I thought of trying using some kind of dynamic SQL, but nothing I came up with was any better than just using the cursor.

I also tried to write a query to do this (like the one below):

select 
    name 
from 
    sys.databases 
where 
    name not in ('master', 'tempdb', 'model', 'msdb') 
    and exists (select top 1 TABLE_NAME 
                from [name].INFORMATION_SCHEMA.TABLES 
                where TABLE_NAME = 'Users') 
    and exists (select top 1 TABLE_NAME 
                from [name].INFORMATION_SCHEMA.TABLES 
                where TABLE_NAME = 'MasterSchedules')

Unsurprisingly, this returns an error

Invalid object name '@name.INFORMATION_SCHEMA.TABLES'

Is there something I can put in place of name to get this to work (or a way to repair my cursor)?

Upvotes: 1

Views: 167

Answers (3)

pmbAustin
pmbAustin

Reputation: 3970

Try this lightly modified version of your code... Use dynamic SQL instead of scripting out "USE" statements, and fixed up your ORDER BY to use a comma instead of a dot... this seemed to work for me, depending on exactly what you want your output to be:

DECLARE @dbname sysname;

DECLARE dbCursor CURSOR FOR 
    SELECT name FROM sys.databases WHERE name not in ('master', 'tempdb', 'model', 'msdb');
OPEN dbCursor;

FETCH NEXT FROM dbCursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @stmt nvarchar(max)
    SELECT @stmt = N'SELECT TABLE_CATALOG, TABLE_NAME FROM ' + @dbname + N'.INFORMATION_SCHEMA.TABLES ' + 
                   N'WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME in (''MasterSchedules'', ''Users'') ' + 
                   N'ORDER BY TABLE_SCHEMA, TABLE_NAME';
    EXEC(@stmt);

    FETCH NEXT FROM dbCursor INTO @dbname
END
CLOSE dbCursor;
DEALLOCATE dbCursor;

Upvotes: 1

O. Gungor
O. Gungor

Reputation: 768

you are going to have to use dynamic sql here and loop through each database without having the need to know the name of each database. assumes you have the proper access to query all the databases on your server:

    use master

    declare @cursor  table (dbname varchar(150))
    declare @dbname  varchar(150)=''
    declare @sql     nvarchar(500)=''

    if object_id('tempdb..#results') is not null drop table #results
    create table #results (dbname varchar(150), tblname varchar(150))

    set nocount on

    insert into @cursor
    select name
    from sys.sysdatabases db
    where name not in ('master', 'tempdb', 'model', 'msdb')

     select @dbname = min(dbname) from @cursor -- first row

     While @dbname Is Not Null
     Begin

        set @sql = ('   
           insert into #results (dbname, tblname)
           select ' + char(39) +  @dbname + char(39) + ' , name from ' + @dbname + '.sys.tables where name in (''MasterSchedules'', ''Users'')
           ')

           exec sp_executesql @sql

      select @dbname = min(dbname) from @cursor where dbname > @dbname -- next row
     End 

     select * from #results

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

Here is a super simple way to do this using dynamic sql. This will return all the databases with those two table names. It doesn't need a loop, cursor or temp table. 

declare @SQL nvarchar(max) = N''

select @SQL = @SQL + N'select DatabaseName = ''' + quotename(name) + ''' 
from ' + quotename(name) + '.sys.tables 
where name in (''MasterSchedules'', ''Users'') 
having COUNT(*) = 2 UNION ALL '
from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb')

set @SQL = left(@SQL, len(@SQL) - 10)

exec sp_executesql @SQL

--EDIT-- You probably want to add the schema into this query. It will work as posted for tables in the default schema but if tables with either name exists in more than 1 schema this will not work as advertised.

Upvotes: 4

Related Questions