Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Question Unanswered: Maintenance Plan T-SQL DROP TABLE utt_*

    Hi all!

    I want to perform a DB cleanup automatically using Maintenance Plan

    Basically I want to DROP all TABLES that starts with this : 'utt_'

    Help please.

    Less is more.
    How long is now?
    http://www.lesouterrain.com

  2. #2
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    it's ok I found my way!

    Thanks
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  3. #3
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    Code:
    -- Declare variable
    SET NOCOUNT ON
    DECLARE @tablename VARCHAR (128)
    
    -- Declare cursor
    DECLARE tables CURSOR FOR
    SELECT TABLE_NAME
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_TYPE = 'BASE TABLE'
    	AND TABLE_NAME LIKE 'utt_%'
    
    -- Open the cursor
    OPEN tables
    
    -- Loop through all the tables in the database
    FETCH NEXT
       FROM tables
       INTO @tablename
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Loop through and drop the unwanted tables
       EXEC ('DROP TABLE dbo.' + @tablename)
       FETCH NEXT
          FROM tables
          INTO @tablename
    END
    
    -- Close and deallocate the cursor
    CLOSE tables
    DEALLOCATE tables

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •