locked
Drop old tables if exists thru Stored Procedure RRS feed

  • Question

  • Hi,

    As part of Server Maintanance in my PROD region, I need to do a auto cleanup on few tables. In one of the database, there is a Daily table, for ex:

    dbo.CILO_CAMP_160101_1800

    dbo.CILO_CAMP_160102_1800

    dbo.CILO_CAMP_160103_1800

    and so on. Here 160101 will contains 01-01-2016 data, 160102 will contains 02-01-2016 data and so on. The table will created daily thru a daily program and it keep creating on the servere. 1800 in the table name is nothing but 18.00 timestamp. I need to drop the tables after 90 days, Meaning on 20160401 (01-04-2016), i have to drop the table dbo.CILO_CAMP_160101_1800, similarly next day on 20160402 (02-04-2016), i have to drop the table dbo.CILO_CAMP_160102_1800. I would like to have a Query that performs the above actions, so that i can create it as a Stored Procudere and run it in SQL schedule JOB. The job will run daily and it will drop the 90 days old table.

          I might have few more daily tables with a different name like (ex: dbo.CardAgreement_160101_1800, dbo.CardAgreement_160101_1800 so on..) but format will be as same as mentioned here.

    The query should find the tablename in the system objects sys.objects, if exists then it should delete, if not exists.

    Could anyone provide me query for the above mentioned.  Please also suggest what are the possible parameters that can be passed to Stored Procedure to make this query easy.

    Thanks in advance!

    Regards,
    Fazlu

    Tuesday, February 2, 2016 1:42 PM

Answers

  • /************************************************************************************************/
    /* dynamicDropTable: Compiles a DROP statement for the given paramerters, and optionally drops  */
    /* the object when @confirmDrop = 1. Ignores date portion when @useDateMiddle = 0               */
    /* Usage: EXEC dynamicDropTable 'CILO_CAMP_', '_1800', 'dbo', 1, 0                              */
    /* Version 1.0 2016-02-02                                                                       */
    /************************************************************************************************/
    
    CREATE PROCEDURE dynamicDropTable (@tablePrefix NVARCHAR(50), @tableSuffix NVARCHAR(50), @schemaName NVARCHAR(50), @useDateMiddle BIT, @confirmDrop BIT)
    AS
    BEGIN
    
    DECLARE @objectName NVARCHAR(250), @dSQL NVARCHAR(MAX)
    
    SET @objectName =  @tablePrefix + CASE WHEN @useDateMiddle = 1 THEN RIGHT(CONVERT(VARCHAR,CURRENT_TIMESTAMP-90,101),2)+LEFT(CONVERT(VARCHAR,CURRENT_TIMESTAMP-90,101),2)+SUBSTRING(CONVERT(VARCHAR,CURRENT_TIMESTAMP-90,101),4,2) ELSE '' END + @tableSuffix
    
    SET @dSQL = 'DROP TABLE ['+@schemaName+'].['+@objectName+']'
    
    IF @confirmDrop = 1
    BEGIN
     IF EXISTS (SELECT 1 FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id AND t.name = @objectName AND s.name = @schemaName)
     BEGIN
      EXEC sp_executeSQL @dSQL
     END
     ELSE
     BEGIN
      PRINT '['+@schemaName+'].['+@objectName+'] does not exist'
     END
    END
    ELSE
    BEGIN
     PRINT 'Command compiled as: ' + @dSQL + ' Execute again with @confirmDrop set to 1 to execute'
    END
    
    END


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Tuesday, February 2, 2016 2:42 PM

All replies

  • Why don't you use a partitioned table?

    Tuesday, February 2, 2016 1:45 PM
  • Hi Stefan,

    The reason could be i guess the dynamic table is created through an IBM Datastage Program daily. The Datastage program is designed to extract data and create tables at run time dynamically. The Datstage program will daily, if it runs today, the job will create table with todays date on the SQL Server.

    Regards,

    Fazlu

    Tuesday, February 2, 2016 1:57 PM
  • Something like this

    DECLARE @TableList varchar(max),@SQL nvarchar(max)

    SET @TableList = STUFF((SELECT ',' + TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE'  AND TABLE_NAME LIKE '%_16%'
    FOR XML PATH('')),1,1,'')

    select @TableList

    SET @Sql = N'DROP TABLE ' + @TableList

    EXEC sp_executesql @SQL,'@TableList varchar(max)',@TableList=@TableList


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 2, 2016 2:08 PM
    Answerer
  • Hi,

    Thanks for your response. I need to drop one table at a time.

    If i run this script today (02-02-2016), i need to check 90 days before dated table. Meaning, i need to check if there any table that exists with CILO_CAMP_151104_1800 only on the database and if this exists on the system table information, then i will drop else do nothing.

    Likewise i  may need to run the same script to delete CardAgreement_151114_1800 and may be in future, we may have few other daily table with same format also to be include in the same process. May be i can use the same procedure by passing the startname as parameter to drop 90th old table of each (CILO_CAMP_151104_1800 & CardAgreement_151114_1800) at a time.

    Kindly let me know if quetion is still confusing, i will try to explain bit in detail if not explained correctly.

    Regards,

    Fazlu

    Tuesday, February 2, 2016 2:23 PM
  • DECLARE @TruncateStatement nvarchar(4000)
    DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
    FOR
    SELECT
        N'DROP TABLE ' +
        QUOTENAME(TABLE_SCHEMA) +
        N'.' +
        QUOTENAME(TABLE_NAME)
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_TYPE = 'BASE TABLE' AND (TABLE_NAME LIKE '%_15%' OR TABLE_NAME LIKE '%_16%'  ) AND
        OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
            N'.' +
            QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
    OPEN TruncateStatements
    WHILE 1 = 1
    BEGIN
        FETCH NEXT FROM TruncateStatements INTO @TruncateStatement
        IF @@FETCH_STATUS <> 0 BREAK
        RAISERROR (@TruncateStatement, 0, 1) WITH NOWAIT
        EXEC(@TruncateStatement)
    END
    CLOSE TruncateStatements
    DEALLOCATE TruncateStatements

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 2, 2016 2:26 PM
    Answerer
  • /************************************************************************************************/
    /* dynamicDropTable: Compiles a DROP statement for the given paramerters, and optionally drops  */
    /* the object when @confirmDrop = 1. Ignores date portion when @useDateMiddle = 0               */
    /* Usage: EXEC dynamicDropTable 'CILO_CAMP_', '_1800', 'dbo', 1, 0                              */
    /* Version 1.0 2016-02-02                                                                       */
    /************************************************************************************************/
    
    CREATE PROCEDURE dynamicDropTable (@tablePrefix NVARCHAR(50), @tableSuffix NVARCHAR(50), @schemaName NVARCHAR(50), @useDateMiddle BIT, @confirmDrop BIT)
    AS
    BEGIN
    
    DECLARE @objectName NVARCHAR(250), @dSQL NVARCHAR(MAX)
    
    SET @objectName =  @tablePrefix + CASE WHEN @useDateMiddle = 1 THEN RIGHT(CONVERT(VARCHAR,CURRENT_TIMESTAMP-90,101),2)+LEFT(CONVERT(VARCHAR,CURRENT_TIMESTAMP-90,101),2)+SUBSTRING(CONVERT(VARCHAR,CURRENT_TIMESTAMP-90,101),4,2) ELSE '' END + @tableSuffix
    
    SET @dSQL = 'DROP TABLE ['+@schemaName+'].['+@objectName+']'
    
    IF @confirmDrop = 1
    BEGIN
     IF EXISTS (SELECT 1 FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id AND t.name = @objectName AND s.name = @schemaName)
     BEGIN
      EXEC sp_executeSQL @dSQL
     END
     ELSE
     BEGIN
      PRINT '['+@schemaName+'].['+@objectName+'] does not exist'
     END
    END
    ELSE
    BEGIN
     PRINT 'Command compiled as: ' + @dSQL + ' Execute again with @confirmDrop set to 1 to execute'
    END
    
    END


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Tuesday, February 2, 2016 2:42 PM