I've list of tables (around 100++) that need to be dropped from SQL Server. Below is the sample code that I would use
IF OBJECT_ID('dbo.DS_Area_TBL', 'U') IS NOT NULL
drop table dbo.DS_Area_TBL
Print 'dbo.DS_Area_TBL has been dropped'
I need to replace table name 100++ time with other table name. How to write a dynamic sql script that can auto generate list of queries?
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT '
IF OBJECT_ID(''' + obj + ''', ''U'') IS NOT NULL BEGIN
DROP TABLE ' + obj + '
PRINT ''' + obj + ' has been dropped''
END
'
FROM (
SELECT obj = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = 'U'
--AND o.name LIKE 'table%'
--AND s.name IN ('dbo')
) t
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
PRINT @SQL
--EXEC sys.sp_executesql @SQL
Output -
IF OBJECT_ID('[dbo].[user_data]', 'U') IS NOT NULL BEGIN
DROP TABLE [dbo].[user_data]
PRINT '[dbo].[user_data] has been dropped'
END
IF OBJECT_ID('[dbo].[formatter_options]', 'U') IS NOT NULL BEGIN
DROP TABLE [dbo].[formatter_options]
PRINT '[dbo].[formatter_options] has been dropped'
END
You could first generate script then execute with dynamic sql:
CREATE TABLE a(a INT);
CREATE TABLE b(a INT);
CREATE TABLE c(a INT);
CREATE TABLE d(a INT);
CREATE TABLE e(a INT);
CREATE TABLE tab(tab_name SYSNAME); -- here are table names stored
INSERT INTO tab VALUES ('a'),('b'),('c'),('d'),('e');
-- main part
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = STUFF((SELECT ' ' + FORMATMESSAGE(
'IF OBJECT_ID(''%s'', ''U'') IS NOT NULL
BEGIN
DROP TABLE %s;
PRINT ''%s has been dropped '';
END
', QUOTENAME(tab_name),QUOTENAME(tab_name),QUOTENAME(tab_name))
FROM tab
FOR XML PATH('')), 1, 1, '');
PRINT @sql; -- for debug
EXEC [dbo].[sp_executesql]
@sql;
If you use version of SQL Server lower than 2012 you need to change FORMATMESSAGE with string concatenation +.
You could easily extend this script with custom schema and so on by modifying template:
'IF OBJECT_ID(''%s'', ''U'') IS NOT NULL
BEGIN
DROP TABLE %s;
PRINT ''%s has been dropped '';
END
'
Output:
IF OBJECT_ID('[a]', 'U') IS NOT NULL
BEGIN
DROP TABLE [a];
PRINT '[a] has been dropped ';
END
IF OBJECT_ID('[b]', 'U') IS NOT NULL
BEGIN
DROP TABLE [b];
PRINT '[b] has been dropped ';
END
IF OBJECT_ID('[c]', 'U') IS NOT NULL
BEGIN
DROP TABLE [c];
PRINT '[c] has been dropped ';
END
IF OBJECT_ID('[d]', 'U') IS NOT NULL
BEGIN
DROP TABLE [d];
PRINT '[d] has been dropped ';
END
IF OBJECT_ID('[e]', 'U') IS NOT NULL
BEGIN
DROP TABLE [e];
PRINT '[e] has been dropped ';
END
EDIT:
How it works:
GROUP_CONCAT in MySQL. You can think about it as a way to combine multiple IF BEGIN END chunks into one string.FORMATMESSAGE will replace %s with actual table names(quoted to avoid SQL Injection attacks)PRINT is for debug to check generated query, can be commentedsp_executesql will execute SQL string
SET NOCOUNT ON
DECLARE @IsDebugMode bit = 0 -- IsDebugMode = 1 will Execute the SQL Script dynamically generated; if not, it will just printed out
, @sql varchar(max) = '' -- To store the final SQL Script dynamically generated.
, @sqltxt varchar(max) = 'IF EXISTS (SELECT * FROM sys.tables WHERE name = ''@TableName'' AND type = ''U'')'+ CHAR(10) + CHAR(11) + 'DROP TABLE @TableName' + CHAR(10) + ';'
;
----[ Create Tables ]
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'TableToDrop1' AND type = 'U')
CREATE TABLE TableToDrop1(TableID int)
;
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'TableToDrop2' AND type = 'U')
CREATE TABLE TableToDrop2(TableID int)
;
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'TableToDrop3' AND type = 'U')
CREATE TABLE TableToDrop3(TableID int)
;
----[ Create CTE Table to store the Tables to Drop just created ]
WITH TableToDropList AS
(
SELECT TableName = T.Name
FROM sys.tables T
WHERE CHARINDEX('TableToDrop',T.Name) > 0
)
----[ Generate the SQL Script in @sql variable ]
SELECT
@sql = @sql + REPLACE(@sqltxt, '@TableName', TableName) + CHAR(10)
FROM TableToDropList
;
IF @IsDebugMode = 0
EXEC(@sql) -- Physically drop the Tables.
ELSE
PRINT @sql -- Print the SQL Script to drop the Tables.
;
