Select databases which only contain specific table

Question

I'm looking for a way to select all databases on my sql server, which only contain the table "dbo.mytable"

How can i do this ?

I already have these two sql queries :

Select name From sys.databases Where database_id > 5

And

IF EXISTS
    (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[mytable]') AND type in (N'U')) 
  Select 1 [Exists]
Else
  Select 0 [Exists]

The first query, lists all databases on my sql server, and the second checks if dbo.mytable exists. I would like to merge them.

Thanks

Martin Smith · Accepted Answer · 2013-05-13T11:44:36.647

15

A concise way that brings them all back in one resultset is

SELECT name
FROM   sys.databases
WHERE  CASE
         WHEN state_desc = 'ONLINE' 
              THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[mytable]', 'U')
       END IS NOT NULL

edited May 13 '13 at 11:44

answered May 13 '13 at 11:24

Martin Smith

438,706
87
741
845

Msg 942, Level 14, State 4, Line 1 Database '...' cannot be opened because it is offline. – Devart May 13 '13 at 11:36
1

It's a good solution, but also need add this: state_desc != 'OFFLINE' – Devart May 13 '13 at 11:37
1

@Devart - Good point. Probably best just to look at ones that are explicitly online. – Martin Smith May 13 '13 at 11:45
+1 i dont know how to thank you. you have done gr8 work for me by this query. – C Sharper Aug 09 '13 at 07:44
instead of 'state_desc' filtering it is possible to use 'state' = 0 http://msdn.microsoft.com/en-us/library/ms178534.aspx – SamFlushing Jun 20 '14 at 12:36

score 4 · Answer 2 · answered May 13 '13 at 10:24

4

You can use sp_Msforeachdb that is an undocumented Stored procedure and run on all databases :

EXEC sp_Msforeachdb "use [?];select * from sys.tables where name='MYTable' "

more about sp_msforeachtable : The undocumented sp_MSforeachdb procedure

answered May 13 '13 at 10:24

Maryam Arshi

1,974
1
19
33

score 4 · Answer 3 · edited May 13 '13 at 10:45

Return list of databases, which contains the specified table -

Query:

DECLARE 
      @SQL NVARCHAR(MAX)
    , @table_name SYSNAME

SELECT @table_name = 'dbo.test'

IF OBJECT_ID('tempdb.dbo.#db_temp') IS NOT NULL
    DROP TABLE #db_temp 

CREATE TABLE #db_temp (db_nm SYSNAME)

SELECT @SQL = (
    SELECT '
        USE [' + d.name + ']; 

        INSERT INTO #db_temp (db_nm)
        SELECT DB_NAME()
        WHERE EXISTS(
            SELECT 1
            FROM sys.objects
            WHERE [object_id] = OBJECT_ID(''' + @table_name + ''')
                AND [type] = ''U''
        )'
    FROM sys.databases d
    WHERE d.name NOT IN ('master', 'tempdb', 'model', 'msdb')
        AND d.state_desc != 'OFFLINE'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

EXEC sys.sp_executesql @SQL

SELECT * 
FROM #db_temp dt

Output:

USE [...]; 

INSERT INTO #db_temp (db_nm)
SELECT DB_NAME()
WHERE EXISTS(
    SELECT 1
    FROM sys.objects
    WHERE [object_id] = OBJECT_ID('...')
        AND [type] = 'U'
)

...

Select databases which only contain specific table

3 Answers3

Linked