Monday, July 16, 2012

How to delete all tables from a Database?

If you want to delete all tables from a database you can help yourself by using this query:

DECLARE @TableName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.tables WHERE type in ('u') 

      OPEN cur

      FETCH NEXT FROM cur INTO @TableName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP TABLE ' + @TableName)
            --select @TableName
            FETCH NEXT FROM cur INTO @TableName
      END
      CLOSE cur
      DEALLOCATE cur 

     
In case you have constraints take special care you delete tables one by one ;)

No comments:

Post a Comment