none
Drop tables by date it were created

    Question

  • Hi,

    I have a large database that were used to archive tables before implementing structural change on it.

    We had this database for years, I want to create a dynamic script to check today's date and drop any table in this database that were created 3 years or older.

    Thanks

    Monday, September 27, 2010 8:17 PM

Answers

  • Hello,

    Adding Code

    SELECT 'DROP TABLE ' + name
    FROM  sys.objects
    WHERE Datediff(YEAR, create_date, Getdate()) > 3
        AND TYPE = 'U' 
    
    

    Thanks

    • Marked as answer by Adam Depp Wednesday, September 29, 2010 12:41 PM
    Monday, September 27, 2010 9:45 PM
  • one more way to get this :

    select       'drop table  [' + s.name +'].[' + t.name +']' , t.create_date  
    from  
        sys
    .tables t 
    inner join 
        sys
    .schemas s 
        on 
        s
    .schema_id = t.schema_id 
    where  
        create_date
    < DATEADD(year,-3, GETDATE()) 
         
    and type='U'

    • Marked as answer by Adam Depp Wednesday, September 29, 2010 12:41 PM
    Tuesday, September 28, 2010 12:25 PM

All replies

  • sys.objects has a create_date column. Pick out all the tables (type = U) with a create date you don't want to keep and generate a drop table statement and execute the statement.
    Tom
    Monday, September 27, 2010 8:26 PM
  • Hello,

    Adding Code

    SELECT 'DROP TABLE ' + name
    FROM  sys.objects
    WHERE Datediff(YEAR, create_date, Getdate()) > 3
        AND TYPE = 'U' 
    
    

    Thanks

    • Marked as answer by Adam Depp Wednesday, September 29, 2010 12:41 PM
    Monday, September 27, 2010 9:45 PM
  • one more way to get this :

    select       'drop table  [' + s.name +'].[' + t.name +']' , t.create_date  
    from  
        sys
    .tables t 
    inner join 
        sys
    .schemas s 
        on 
        s
    .schema_id = t.schema_id 
    where  
        create_date
    < DATEADD(year,-3, GETDATE()) 
         
    and type='U'

    • Marked as answer by Adam Depp Wednesday, September 29, 2010 12:41 PM
    Tuesday, September 28, 2010 12:25 PM