Abdullah Abdullah - 1 year ago 72
SQL Question

Delete Data from all tables in Sqlserver Database except some tables

I have A SQLSERVER Database, I want to Delete all tables except some tables
i use use this script

EXEC sp_MSforeachtable 'IF OBJECT_ID(''?'') NOT IN (
ISNULL(OBJECT_ID(''[dbo].[T1]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T2]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T3]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T4]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T5]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T6]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T7]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T8]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T9]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T10]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T11]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T12]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T13]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T14]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T15]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T16]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T17]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T18]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T19]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T20]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T21]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T22]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T23]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T24]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T25]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T26]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T27]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T28]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T29]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T30]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T31]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T32]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T33]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T34]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T35]''),0)
)
DELETE FROM ?'


the sqlserver return this error message
"Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'ISN'."


I think the problem may be about the number of tables which is excepted

Answer Source

Try to do it another way:

DECLARE @command nvarchar(max); 

--Remove spaces in front of ,ISNULL 
SELECT @command = N'IF OBJECT_ID(''?'') NOT IN (
 ISNULL(OBJECT_ID(''[dbo].[T1]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T2]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T3]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T4]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T5]''),0)
...
,ISNULL(OBJECT_ID(''[dbo].[TN]''),0)
)
DELETE FROM ?'; 

EXEC sp_MSforeachtable @command;

NOTE: This SP works only with nvarchar(2000) in first command (source).

It is the first command to be executed by this Stored Procedure and the data type is nvarchar(2000).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download