Tuesday, 15 August 2017

Drop a table in all the databases in SQL Server

Suppose we get the requirement that we need to Drop a table in all database on the server. If we have small number of databases in server we can connect each database and run the drop table, but there are more database like 100+ databases then it is very difficult to drop. Using below script will drop a table in entire database.
DECLARE @DDL VARCHAR(MAX)
SET @DDL='Drop table Audit'
DECLARE @DatabaseName AS VARCHAR(500)
--DECLARE CURSOR
DECLARE CUR CURSOR
FOR
--Choose the DBs on which we 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


Before running the script.
 
After running this script.


No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts