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

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.


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 

How to find if Table exists in all the databases in SQL Server

1 comment: