如何在T-SQL中验证DROP TABLE与同表CREATE TABLE语句成对出现

要在T-SQL运行时验证动态SQL文本中DROP TABLE与CREATE TABLE的成对一致性，核心是提取两类语句的目标表名并做集合比对。以下是纯T-SQL实现方案：

实现思路 #

1. 从待验证的SQL文本中提取所有有效DROP TABLE语句的表名（排除注释中的语句）
2. 从同一文本中提取所有有效CREATE TABLE语句的表名（排除注释中的语句）
3. 检查每个DROP TABLE的表名，必须能在CREATE TABLE的表名集合中找到匹配项

纯T-SQL实现代码 #

第一步：创建表名提取的辅助函数 #

这个函数负责从SQL文本中提取指定关键字（DROP TABLE/CREATE TABLE）对应的表名，同时忽略注释内容：

CREATE OR ALTER FUNCTION dbo.ExtractTableNames(
    @SqlText NVARCHAR(MAX),
    @Keyword NVARCHAR(50) -- 传入 'DROP TABLE' 或 'CREATE TABLE'
)
RETURNS @TableNames TABLE (QualifiedName NVARCHAR(256))
AS
BEGIN
    -- 先移除注释内容：单行注释(--)和多行注释(/* */)
    DECLARE @CleanedSql NVARCHAR(MAX) = @SqlText;
    
    -- 移除多行注释
    WHILE PATINDEX('%/*%*/%', @CleanedSql) > 0
        SET @CleanedSql = STUFF(@CleanedSql, PATINDEX('%/*%*/%', @CleanedSql), CHARINDEX('*/', @CleanedSql, PATINDEX('%/*%*/%', @CleanedSql)) - PATINDEX('%/*%*/%', @CleanedSql) + 2, '');
    
    -- 移除单行注释
    WHILE PATINDEX('%--%', @CleanedSql) > 0
        SET @CleanedSql = STUFF(@CleanedSql, PATINDEX('%--%', @CleanedSql), LEN(@CleanedSql) - PATINDEX('%--%', @CleanedSql) + 1, '');

    -- 提取指定关键字后的表名
    DECLARE @StartPos INT, @EndPos INT, @TableName NVARCHAR(256);
    SET @StartPos = PATINDEX('%' + @Keyword + '%', @CleanedSql);
    
    WHILE @StartPos > 0
    BEGIN
        -- 定位关键字后的第一个非空白字符
        SET @StartPos = @StartPos + LEN(@Keyword);
        SET @StartPos = PATINDEX('%[^ ]%', SUBSTRING(@CleanedSql, @StartPos, LEN(@CleanedSql))) + @StartPos - 1;
        
        -- 定位表名的结束位置（遇到空格、括号、分号等分隔符）
        SET @EndPos = PATINDEX('%[ ;(),]%', SUBSTRING(@CleanedSql, @StartPos, LEN(@CleanedSql)));
        IF @EndPos = 0 SET @EndPos = LEN(@CleanedSql) - @StartPos + 1;
        
        SET @TableName = LTRIM(RTRIM(SUBSTRING(@CleanedSql, @StartPos, @EndPos - 1)));
        INSERT INTO @TableNames VALUES (@TableName);
        
        -- 继续查找下一个关键字
        SET @CleanedSql = SUBSTRING(@CleanedSql, @StartPos + @EndPos - 1, LEN(@CleanedSql));
        SET @StartPos = PATINDEX('%' + @Keyword + '%', @CleanedSql);
    END
    
    RETURN;
END;

第二步：验证逻辑实现 #

在存储过程中调用上述函数，完成表名比对：

CREATE OR ALTER PROCEDURE dbo.ValidateDropCreateConsistency(
    @SqlText NVARCHAR(MAX),
    @IsValid BIT OUTPUT,
    @ErrorMessage NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;
    SET @IsValid = 1;
    SET @ErrorMessage = '';

    -- 提取所有DROP TABLE的表名
    DECLARE @DropTables TABLE (QualifiedName NVARCHAR(256));
    INSERT INTO @DropTables
    SELECT QualifiedName FROM dbo.ExtractTableNames(@SqlText, 'DROP TABLE');

    -- 如果没有DROP TABLE语句，直接通过验证
    IF NOT EXISTS(SELECT 1 FROM @DropTables)
        RETURN;

    -- 提取所有CREATE TABLE的表名
    DECLARE @CreateTables TABLE (QualifiedName NVARCHAR(256));
    INSERT INTO @CreateTables
    SELECT QualifiedName FROM dbo.ExtractTableNames(@SqlText, 'CREATE TABLE');

    -- 检查每个DROP的表是否有对应的CREATE
    DECLARE @MissingTables NVARCHAR(MAX);
    SELECT @MissingTables = STRING_AGG(QualifiedName, ', ')
    FROM @DropTables d
    WHERE NOT EXISTS(SELECT 1 FROM @CreateTables c WHERE c.QualifiedName = d.QualifiedName);

    IF @MissingTables IS NOT NULL
    BEGIN
        SET @IsValid = 0;
        SET @ErrorMessage = '以下表仅存在DROP TABLE语句，缺少对应CREATE TABLE：' + @MissingTables;
    END
END;

测试示例 #

符合要求的情况 #

DECLARE @good_text NVARCHAR(MAX) = '
    DROP TABLE dbo.my_table
    CREATE TABLE dbo.my_table (
        col1 INT,
        col2 CHAR(1)
    )
';
DECLARE @IsValid BIT, @ErrorMessage NVARCHAR(MAX);
EXEC dbo.ValidateDropCreateConsistency @good_text, @IsValid OUTPUT, @ErrorMessage OUTPUT;
SELECT @IsValid AS IsValid, @ErrorMessage AS ErrorMessage;
-- 输出：IsValid=1，ErrorMessage=''

不符合要求的情况1：仅含DROP TABLE #

DECLARE @bad_text1 NVARCHAR(MAX) = '
    DROP TABLE dbo.my_table
';
DECLARE @IsValid BIT, @ErrorMessage NVARCHAR(MAX);
EXEC dbo.ValidateDropCreateConsistency @bad_text1, @IsValid OUTPUT, @ErrorMessage OUTPUT;
SELECT @IsValid AS IsValid, @ErrorMessage AS ErrorMessage;
-- 输出：IsValid=0，ErrorMessage='以下表仅存在DROP TABLE语句，缺少对应CREATE TABLE：dbo.my_table'

不符合要求的情况2：表名不匹配 #

DECLARE @bad_text2 NVARCHAR(MAX) = '
    DROP TABLE dbo.my_table
    CREATE TABLE dbo.different_table_name (
        col1 INT,
        col2 CHAR(1)
    )
';
DECLARE @IsValid BIT, @ErrorMessage NVARCHAR(MAX);
EXEC dbo.ValidateDropCreateConsistency @bad_text2, @IsValid OUTPUT, @ErrorMessage OUTPUT;
SELECT @IsValid AS IsValid, @ErrorMessage AS ErrorMessage;
-- 输出：IsValid=0，ErrorMessage='以下表仅存在DROP TABLE语句，缺少对应CREATE TABLE：dbo.my_table'

注意事项 #

• 该实现会忽略注释中的DROP/CREATE语句，避免误判
• 支持带架构名（如dbo.table）和不带架构名的表名提取
• 若需要处理更复杂的SQL语法（如带方括号的表名[my table]），可以修改函数中的表名结束位置判断逻辑，加入对]的识别

内容的提问来源于stack exchange，提问作者Simon.S.A.