sql - 动态sql脚本生成删除表语句列表

Question

我有需要从 SQL Server 删除的表列表(大约 100++)。下面是我将使用的示例代码

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'

我需要用其他表名替换表名 100++ 次。如何编写一个可以自动生成查询列表的动态sql脚本？

Answer 1

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

输出 -

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