删除Sqlserver数据库中除部分表外的所有表中的数据

Question

我有一个SQLSERVER数据库，我想删除所有表，除了一些表我使用这个脚本

  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 ?'

sqlserver返回此错误消息"Msg 102, Level 15, State 1, Line 22 Incorrect syntax near 'ISN'."

我想问题可能出在表的数量上

Answer 1

试着用另一种方法:

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;

注意:此SP仅适用于第一个命令(源)中的nvarchar(2000)。

该存储过程执行的第一个命令，数据类型为nvarchar(2000)。

Answer 2

EXEC sp_MSforeachtable 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].[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 ?'

问题是sp_msforeachtable的第一个参数长度为2000，您的查询传递到该参数。由于您的查询长度超过此长度，因此不会将其全文传递给proc。这就是错误的原因。通过删除一些换行符来缩短查询的长度。Try my query its working