后续-存档存储过程

好的,我开始的前一个线程是 Archival stored procedures 但这是一个后续问题。我所拥有的是一个工作存储过程,它创建了活动数据库的精确副本,但是,当我想删除上个月的旧列时,该过程就在那里出现了一个错误:

Msg 102,第15级,状态1,第3行

以下是完整程序的代码:

declare @tablename varchar(500)
declare @sql varchar(5000)
declare @idname varchar(50)
declare @tablearchive varchar(500)
declare @lastmonth nvarchar(MAX)

SET @lastmonth = 'D' + cast(year(getdate()) as char(4)) + right('0' + cast(month(getdate())-1 as varchar), 2) + '__'
--Select all the tables which you want to make in archive

declare tableCursor cursor FAST_FORWARD FOR 
    SELECT table_name 
    FROM INFORMATION_SCHEMA.TABLES
    WHERE table_name = 'CVECountsByDate'

--Put your condition, if you want to filter the tables
--like '%TRN_%' and charindex('Archive',table_name) = 0 and charindex('ErrorLog',table_name) = 0
--Open the cursor and iterate till end
OPEN tableCursor 
FETCH NEXT FROM tableCursor INTO @tablename     

WHILE @@FETCH_STATUS = 0
BEGIN
    set @tablearchive =  @tablename+'_Archive'

    --check for the table exists, not, create it
    IF not EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME= @tablearchive) 
    BEGIN
        SET @sql = 'select * into ' + @tablearchive +' from '+ @tablename +' where 1=2'
        EXEC(@sql)
    END

    --check the structure is same, if not, create it
    IF exists (select column_name from 
            INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablename and column_name not in (select column_name from INFORMATION_SCHEMA.COLUMNS 
            where TABLE_NAME=@tablearchive))
    BEGIN
        SET @sql = 'drop table ' + @tablearchive
        EXEC(@sql)

        SET @sql = 'select * into ' + @tablearchive +' from '+ @tablename +' where 1=2'
        EXEC(@sql)
    END

    --Check if the table contains, identify column,if yes, then it should be handled in different way
    --You cannot remove the identity column property through T-SQL
    --Since the structure of both tables are same, the insert fails, as it cannot insert the identity column
    --value in the archive table
    IF EXISTS(SELECT *      FROM information_schema.tables      WHERE table_name = @tablename AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),
            'TableHasIdentity') != 0)
    BEGIN
        --Select the identity column name automatically             
        select @idname = column_name   
        from information_schema.columns 
        where columnproperty(object_id(table_name), column_name, 'isidentity') = 1 
          and table_name = @tablearchive

        --Remove the column
        SET @sql = 'ALTER TABLE ' + @tablearchive + ' DROP COLUMN ' + @idname
        EXEC(@sql)

        --Create the column name again (not as identity)
        --archive table does require identity column
        SET @sql = 'ALTER TABLE ' + @tablearchive + ' ADD ' + @idname+ ' INT'
        EXEC(@sql)
    END

    SET @sql = 'insert into ' + @tablearchive +' select * from '+ @tablename
    EXEC(@sql)

    FETCH NEXT FROM tableCursor INTO @tablename 

    declare @PrevMonth nvarchar(MAX) = 'D' + cast(year(getdate()) as char(4)) + right('0' + cast(month(getdate())-1 as varchar), 2) + '__'
    DECLARE @DynSql nvarchar(MAX) = 'ALTER TABLE ' + @tablename + ' DROP  COLUMNS IF EXISTS 
    (SELECT COLUMN_NAME Columns FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' + @tablename + ' 
AND COLUMN_NAME LIKE ' + @PrevMonth + ''

    SELECT (@DynSql)
    EXEC(@DynSql)
END

CLOSE tableCursor
DEALLOCATE tableCursor