SQL Server: Tips and Tricks - 2 (2017)

How to check if table exists in all databases in SQL Server

Scenario:

You are working as SQL Server DBA / Developer. You have many databases on SQL Server Instance and you would like to check in which databases table exists or does not exists.

Solution:

Declare @TableName VARCHAR(128)

--Provide the Table Name that you would like to Check

SET @TableName='MyTestTable'

--Drop Temp table if exists to save record

IF OBJECT_ID('tempdb..##RecordCount') IS NOT NULL

DROP TABLE ##RecordCount

CREATE TABLE ##RecordCount (

ServerName VARCHAR(128),

DatabaseName VARCHAR(128)

,TableName VARCHAR(128)

,IsExists VARCHAR(10)

)

--Use Cursor to Loop through Databases

DECLARE @DatabaseName AS VARCHAR(500)

DECLARE CDCCursor CURSOR

FOR

SELECT NAME

FROM sys.databases

where database_id>4

OPEN CDCCursor

FETCH NEXT

FROM CDCCursor

INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @DBName AS NVARCHAR(500);

SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

Print @DbName

--USE Dynamic SQL To change the Database Name

EXEC (

N'USE ' + @DBName + N'; EXEC(''

Insert into ##RecordCount

Select @@ServerName,DB_Name() AS DatabaseName,

'''''+@TableName+''''',(Select CASE WHEN count(*)>1

then ''''YES'''' ELSE ''''NO'''' END AS IsExist

from INFORMATION_SCHEMA.TABLES

where TABLE_NAME='''''+@TableName+'''''

and Table_Type=''''BASE TABLE'''')

'');'

);

FETCH NEXT

FROM CDCCursor

INTO @DatabaseName

END

CLOSE CDCCursor

DEALLOCATE CDCCursor

--Return if Table Exists

SELECT *

FROM ##RecordCount