sql-servert-sql

Check that text contains drop and create statements for the same table


Within T-SQL, commands like EXEC and SP_EXECUTESQL allow us to execute text strings that contain code. I would like to run a validation across text strings before execution to require that if the text string drops a table then it must also recreates that table.

So this text string would pass:

DECLARE @good_text = '
    DROP TABLE dbo.my_table
    CREATE TABLE dbo.my_table (
        col1 INT,
        col2 CHAR(1)
    )
'

And this text would fail as there is no create table statement:

DECLARE @bad_text = '
    DROP TABLE dbo.my_table
'

And this text would fail because the two table have different names:

DECLARE @bad_text = '
    DROP TABLE dbo.my_table
    CREATE TABLE dbo.different_table_name (
        col1 INT,
        col2 CHAR(1)
    )
'

If I did not need to match the table name, then it would be straight forward to check @text LIKE '%DROP TABLE%' and @text LIKE '%CREATE TABLE%'.

Something like:

SET @contains_drop = IIF(@text LIKE '%DROP TABLE%', 1, 0)
SET @contains_create = IIF(@text LIKE '%CREATE TABLE%', 1, 0)
IF NOT (@contains_drop = 1 AND @contains_create = 0)
    EXEC @text

But these statements don't extend easily to include the table name. So this method would incorrectly pass the case when the drop and create table names differ.

Outside of SQL I would do this with regex/grep but I am not sure how to do so within SQL.

(Yes, I know there are good reasons not to pass this kind of text to EXEC or SP_EXECUTESQL. But changing this is not within the current scope, hence why I want to create this additional validation.)

No need for answers to handle table_name vs [table_name] or DROP TABLE IF EXISTS vs DROP TABLE. I can add all of that text cleaning separately.

Edit with additional details

One of our production tasks involves running a significant number of scripts. Rather than do these manually, we have designed a procedure that loops over a metadata table containing all the file names: loads the code within each script (into a variable like @text above), validates, and then executes the script (using EXEC or SP_EXECUTESQL).

As part of the validation, we would like to be sure that DROP TABLE statements never occur without a recreating CREATE TABLE statement. Ideally this validation occurs at runtime so we can be confident it is always passed before execution.

I am not concerned about malicious code execution. My concern is more to catch mistakes if a script is updated and there is a typo in the table name, or a maintainer includes one statement but fails to include the other.

While I could use a different programming language to validate the scripts, a maintainer could forget to run this or could ignore its results. Hence, the preference to valid at runtime.

While there are alternatives to dropping and recreating a table, not all the maintainers have the skill to use the alternative approaches. And some of those alternatives should have the same validation (for example, I expect to use the same method to check TRUNCATE commands are paired with INSERT INTO commands).


Edit following answer

It seems like most of the comments & critique are focused on handling the problem in the most general of situations "of all the ways we could drop and create tables, how can we detect if the same table is dropped and created". This was not the intention of the question - though I can see why it was interpreted this way - hopefully this edit clarifies.

The intended interpretation is: "In the specific instance when tables are dropped and created in the same piece of text using the DROP TABLE and CREATE TABLE commands, how can I be sure both commands occur together and both refer to the same table?"


Solution

  • Hi I think this can solve your issue:

    Explanation: What I did is that I prepared 2 temp tables and stored all the table names in them for dropped and created. Then joined those table and check for null in one and in case of null there is a mismatch.

    Scenarios to fail :

    1. If table has space in its name.

    Handled a scenario where if drop is after create then also fails. Schema is also handled.

    DECLARE @good_text nvarchar(MAX) =
    '   DROP TABLE dbo.my_table1 asdf
        CREATE TABLE dbo.my_table1 (col1 INT,col2 CHAR(1)) 
        CREATE TABLE dbo.my_table2 (col1 INT,col2 CHAR(1))
        DROP TABLE dbo.my_table2
        adf
        DROP TABLE dbo.my_table3 1234ea sdf
        CREATE TABLE dbo.my_table3 (col1 INT,col2 CHAR(1))'
    --Select Charindex('Drop Table',@good_text)+10
    
    --Remove Line changes from the script for ease of parsing
    SELECT
        @good_text = Replace(Replace(@good_text,Char(13),' '),char(10),' ')
    
    --Remove doulble spaces from the script
    WHILE CHARINDEX('  ',@good_text) > 0
    BEGIN
        SET @good_text = Replace(@good_text,'  ',' ')
    END
    
    DECLARE @drop1   nvarchar(MAX) = REPLACE(@good_text,'DROP TABLE','~')
    ,       @create1 nvarchar(MAX) = REPLACE(@good_text,'CREATE TABLE','~')
    DECLARE @dropcount   int = LEN(@drop1) - len(Replace(@drop1, '~',''))
    ,       @createCount int = LEN(@create1) - len(Replace(@create1, '~',''))
    --Select @dropcount, @createCount
    IF object_id('tempdb..#drop') IS NOT NULL
        DROP TABLE #drop
    IF object_id('tempdb..#Create') IS NOT NULL
        DROP TABLE #Create
    
    CREATE TABLE #drop (
          id         int IDENTITY
        , table_name varchar(1000)
        , WordLoc    INT
        , tSchema    varchar(100) )
    CREATE TABLE #Create (
          id         int IDENTITY
        , table_name varchar(1000)
        , WordLoc    INT
        , tSchema    varchar(100) )
    
    DECLARE @TableName varchar(100) 
    ,       @NewString nvarchar(MAX)
    ,       @count     int           = 0
    ,       @storeText nvarchar(MAX) = @good_Text
    
    --Fill Drop table
    WHILE (@count < @dropcount)
    BEGIN
        SELECT
            @TableName =
            SUBSTRING(@good_text
            ,(Charindex('Drop Table',@good_text)+10)
            ,Charindex(' ',(SUBSTRING(@good_text,Charindex('Drop Table',@good_text)+11,LEN(@good_text))))
            )
        SELECT
            @NewString = SUBSTRING(@good_text, CHARINDEX('Drop Table'+@TableName,@good_text)+len(@TableName),len(@good_text))
    
        INSERT INTO #drop ( table_name, WordLoc )
            SELECT
                  @TableName
                , CHARINDEX('Drop Table'+@TableName,@storeText)
    
        SELECT
            @good_text = @NewString
        --select @NewString
        SET @count += 1
    END
    
    SELECT
        @good_text = @storeText
    
    --Fill Create table
    SET @count = 0
    WHILE (@count < @createCount)
    BEGIN
        SELECT
            @TableName =
            SUBSTRING(@good_text
            ,(Charindex('Create Table',@good_text)+12)
            ,Charindex(' ',(SUBSTRING(@good_text,Charindex('Create Table',@good_text)+13,LEN(@good_text))))
            )
        SELECT
            @NewString = SUBSTRING(@good_text, CHARINDEX('Create Table'+@TableName,@good_text)+len(@TableName),len(@good_text))
    
        INSERT INTO #Create ( table_name, WordLoc )
            SELECT
                  @TableName
                , CHARINDEX('Create Table'+@TableName,@storeText)
    
        SELECT
            @good_text = @NewString
        --select @NewString
        SET @count += 1
    END
    
    DECLARE @TableTRIM nvarchar(100) = '#drop'
    --Ltrim and rtrim all the data
    IF OBJECT_ID('tempdb..##check') IS NOT NULL
    BEGIN
        DROP TABLE ##check
    END
    EXEC ('SELECT name, 0 AS done INTO ##check FROM tempdb.sys.columns WHERE OBJECT_ID = OBJECT_ID(''tempdb..'+@TableTRIM+''') AND is_identity = 0 AND system_type_id NOT IN (SELECT system_type_id FROM sys.types WHERE Name = ''float'')')
    WHILE ((SELECT
                count(*)
            FROM ##check
            WHERE done = 0)<>0)
    BEGIN
        DECLARE @col VARCHAR(100) = (SELECT TOP 1
                                        '['+name+']'
                                     FROM ##check
                                     WHERE done = 0)
        EXEC ('UPDATE '+@TableTRIM+' SET '+@col+' = ltrim(rtrim(REPLACE('+@col+',''"'','''')))')
        UPDATE ##check
        SET done = 1
        WHERE '['+name+']' = @col
    END
    
    SET @TableTRIM = '#Create'
    --Ltrim and rtrim all the data
    IF OBJECT_ID('tempdb..##check') IS NOT NULL
    BEGIN
        DROP TABLE ##check
    END
    EXEC ('SELECT name, 0 AS done INTO ##check FROM tempdb.sys.columns WHERE OBJECT_ID = OBJECT_ID(''tempdb..'+@TableTRIM+''') AND is_identity = 0 AND system_type_id NOT IN (SELECT system_type_id FROM sys.types WHERE Name = ''float'')')
    WHILE ((SELECT
                count(*)
            FROM ##check
            WHERE done = 0)<>0)
    BEGIN
        SET @col = (SELECT TOP 1
                        '['+name+']'
                    FROM ##check
                    WHERE done = 0)
        EXEC ('UPDATE '+@TableTRIM+' SET '+@col+' = ltrim(rtrim(REPLACE('+@col+',''"'','''')))')
        UPDATE ##check
        SET done = 1
        WHERE '['+name+']' = @col
    END
    
    UPDATE #drop
    SET tSchema = SUBSTRING(table_name,1,CHARINDEX('.',table_name))
    UPDATE #Create
    SET tSchema = SUBSTRING(table_name,1,CHARINDEX('.',table_name))
    
    UPDATE #drop
    SET tSchema = 'dbo.'
    WHERE isnull(tSchema,'') = ''
    UPDATE #Create
    SET tSchema = 'dbo.'
    WHERE isnull(tSchema,'') = ''
    
    UPDATE #drop
    SET table_name = REPLACE(table_name,tSchema,'')
    UPDATE #Create
    SET table_name = REPLACE(table_name,tSchema,'')
    
    IF EXISTS (
        SELECT
            *
        FROM            #drop   D
        FULL OUTER JOIN #Create C ON C.table_name = D.table_name
            AND c.tSchema = D.tSchema
            AND D.WordLoc < C.WordLoc --If you don't want to fail if drop is after create then comment this section
        WHERE C.id IS NULL
            OR d.id IS NULL
        )
        RAISERROR('Script contains mismatch drop and create',16,1)
    

    The Fiddle for experimentations

    Let me know if you find any different scenarios in this to fail so that I can update the code.

    Thanks