Search code examples
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