How to delete all tables from a database in SQL Server 2005 in a statement except one table

Question

I use sql-server 2005. Initially, for the purpose of training, I created a lot of tables, now I want to discard all these tables except the ones that I currently work in. Is there any code or query with which I can delete all tables from my database except one useful table. One way, in my opinion, is to copy this table to a new database using the SELECT INTO... and delete this database, but I don't know if this is a good way to solve this problem.

Answer 1

If the tables are not very large, I would probably just select all of them in the "Object Explorer Details" window in SSMS, and then delete delete.

For a software solution, you can use

 EXEC sys.sp_MSforeachtable N'IF OBJECT_ID(''?'') <> OBJECT_ID(''dbo.YourTableToKeep'') DROP TABLE ? ' 

Both methods may require repeated runs to delete all tables associated with FK relationships (a table cannot be deleted if the other table has an FK reference to it).

Answer 2

You can use sql to create all the drops you need. For example, the following sql query

 USE [MyDatabase]; GO SELECT replace( replace('DROP TABLE [{Schema}].[{TableName}];' ,'{Schema}',TABLE_SCHEMA) ,'{TableName}',TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT IN ('TableNameOne','TableNameTwo') 

and get the result of the query.

Copy the first column of the query and paste it into a new query window.

It is easy to see that you get a list of quotes for all tables except "TableNameOne" and "TableNameTwo", ...

It seems to me that it is very simple ...

Answer 3

 /* Drop all Foreign Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) WHILE @name is not null BEGIN SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) WHILE @constraint IS NOT NULL BEGIN SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']' EXEC (@SQL) PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) END GO /* Drop all Primary Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) WHILE @name IS NOT NULL BEGIN SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) WHILE @constraint is not null BEGIN SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']' EXEC (@SQL) PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) END GO /* Drop all tables */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Table: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) END GO