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

2 comments:

  1. Thank you for your valuable content , Easy to understand and follow. As said, the migration to cloud is very essential for the protection of the database.

    Cloud Migration services
    Aws Cloud Migration services
    Azure Cloud Migration services
    Vmware Cloud Migration services
    Database Migration services
    Lia Infraservices

    ReplyDelete
  2. The above-mentioned skills if incorporated by an SEO article writer then it is good for a business which seeks professional services from the writer Go Here Whenever you search something on the web, Google's calculation helps to pick stories as well as posts that will address your inquiry.

    ReplyDelete