Holistic Systems

The following SQL script will delete all tables from a Microsoft SQL Server Database.

After execution of this script, all of your data (and data tables) will be gone. Appropriate care should be exercised.

The current version of this script will not remove any tables that are referenced by foreign keys. You must remove any foreign keys by hand before running the script.

If you’re not sure, just run the script and then examine any remaining tables and remove any foreign keys. Then run the script again.

DECLARE tables_cursor CURSOR
FOR SELECT name FROM sysobjects WHERE type = ‘U’

OPEN tables_cursor
DECLARE @tablename sysname

FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC (‘DROP TABLE ‘ + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END

DEALLOCATE tables_cursor

PRINT ‘All user-defined tables have been dropped from the database.’

This entry was posted on Tuesday, October 2nd, 2007 at 12:39 pm and is filed under SQL programming. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.