How to Drop Table from All the Databases from SQL Server

Scenario : How to Drop Table from All the Databases from SQL Server 

You are working as SQL server developer or SQL server DBA. You got this requirement " You need to create the scripts to drop the table from all the databases if exists in SQL Server". If you have small number of databases, let's say 5 or 10, It it not a bid deal to go to each Database and drop the table. But think about  a situation when you have 100's of databases and you have to drop the table from each database if exists. 

I have used the cursor with dynamic sql to loop through the databases and drop the drop if exists. 

You can filter the databases for which you want to run the script by add more filters to where clause in sys.database part of the code.

The below code is going to check if table exists and if it does, it is going to drop. I will suggest to run the script on Development, SIT, QA environments first and then on Prod. 

--Provide the DDL Statment that you would like to run to drop the table if exists in Database
  --Change the schema and table name in script as per your requirement
    DECLARE @DDL VARCHAR(MAX)
    SET @DDL='IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = ''''dbo''''
                 AND  TABLE_NAME = ''''MyNewTableName''''))
BEGIN
   Drop table dbo.MyNewTableName
END'
    DECLARE @DatabaseName AS VARCHAR(128)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

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

        --USE Dynamic SQL To Change DB name and run DDL statement to drop table
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR