Select Databases Which Only Contain Specific Table

Select databases which only contain specific table

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 

Display all the names of databases containing particular table

I got it done through following query:

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

Select all databases that have a certain table and a certain column

When i want to loop through all databases, i do a loop like the following. Its easy to follow:

DECLARE @dbs TABLE ( dbName NVARCHAR(100) )
DECLARE @results TABLE ( resultName NVARCHAR(100) )

INSERT INTO @dbs
SELECT name FROM sys.databases

DECLARE @current NVARCHAR(100)

WHILE (SELECT COUNT(*) FROM @dbs) > 0
BEGIN
    SET @current = (SELECT TOP 1 dbName FROM @dbs)

    INSERT INTO @results
    EXEC
    (
        'IF EXISTS(SELECT 1 FROM "' + @current + '".INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = ''Target_Table_Name'' AND COLUMN_NAME = ''Target_Column_Name'')
        BEGIN
            --table and column exists, execute query here
            SELECT ''' + @current + '''
        END'
    )

    DELETE FROM @dbs
    WHERE dbName = @current
END

SELECT * FROM @results

How to find, across multiple databases, a specific table (common to most/all) that is not empty

In both methods replace <tablename> with the table name

Using sp_foreachdb

You can use sp_foreachDb

CREATE TABLE ##TBLTEMP(dbname varchar(100), rowscount int)

DECLARE @command varchar(4000) 
SELECT @command = 
'if exists(select 1 from [?].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =''<TABLE NAME>'') insert into ##TBLTEMP(dbname,rowscount) select ''[?]'',count(*) from [?].dbo.<tablename>' 
EXEC sp_MSforeachdb @command

SELECT * FROM ##TBLTEMP WHERE rowscount > 0

DROP TABLE ##TBLTEMP

Using CURSOR

CREATE TABLE ##TBLTEMP(dbname  varchar(100), rowscount int)
DECLARE @dbname  Varchar(100), @strQuery varchar(4000)

DECLARE csr CURSOR FOR SELECT [name] FROM sys.databases
FETCH NEXT FROM csr INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @strQuery = 'if exists(select 1 from [' + @dbname +'].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =''<TABLE NAME>'') INSERT INTO ##TBLTEMP(dbname,rowscount) SELECT ''' + @dbname + '' ', COUNT(*) FROM [' + @dbname + '].[dbo].<table name>'

EXEC(@strQuery)

FETCH NEXT FROM csr INTO @dbname

END

CLOSE csr
DEALLOCATE csr

SELECT * FROM ##TBLTEMP where rowscount > 0

References

• Sp MSforeachDB
• Run same command on all SQL Server databases without cursors
• DECLARE CURSOR (Transact-SQL)

How to find all tables and datasets/databases which have a specific column name in big query

I found the solution is to replace the dataset name with region-us instead.

The below works for looking up across tables and datasets

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.TABLES
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%' 

The below works for views:

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.VIEWS
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%' 

Find a database with a particular table OR Find a table in every database of SQL Server

Okay, if you're just wanting to find each database that contains a particular table, and aren't going to be querying the table, then you can just do:

create table #t (
    DBName sysname not null
)
go
exec sp_MSforeachdb 'use [?]; if OBJECT_ID(''dbo.mytable'') is not null insert into #t (DBName) select ''?'''
go
select * from #t
go
drop table #t

(If you're not using multiple schemas in your databases, you won't need to specify dbo in the OBJECT_ID call, otherwise I use it to avoid finding tables in the wrong schema)

Select tables in database and group them by partial name

You can use string operations:

SELECT DISTINCT substring_index(substring_index(TABLE_NAME, '_', 2), '_', -1)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = database_name

---

Related Topics