Geek Answers Handbook

Delete all but a few tables in the SQL Server database

I have about 50+ tables in my database, now what I want is to delete all the tables in the database, except for a few.

now what i know is sys.tables is a table listing all the tables, so initially I ran a query like this

 delete from sys.tables where name like '%DynamicSurgery' (or any other condition)

thinking it might work. But, as I expected, this causes an error like

Special updates to system directories are not allowed.

Please tell me if there is a way to remove multiples in SQL Server?

+7
source share
3 answers

You can use dynamic query to DROP required tables:

 DECLARE @ExecSQL AS NVARCHAR (MAX) = ''; SELECT @ExecSQL = @ExecSQL + 'DROP TABLE ' + QUOTENAME(S.name) + '.' + QUOTENAME(T.name) + '; ' FROM sys.tables T JOIN sys.schemas S ON S.schema_id = T.schema_id WHERE T.name LIKE '%DynamicSurgery' --PRINT @ExecSQL EXEC (@ExecSQL)
+13
source 
  EXEC sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'; EXEC sp_MSforeachtable 'IF OBJECT_ID(''?'') NOT IN ( ISNULL(OBJECT_ID(''[dbo].[Table1]''),0), ISNULL(OBJECT_ID(''[dbo].[Table2]''),0) ) DELETE FROM ?'; EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL';
0
source
  • select a table by clicking on it.
  • press the "Delete" button and press "Enter". Please note: if there are any dependencies (Foreign Key), the table will not be deleted.
-5
source

More articles:


All Articles