SQL删除用户表的问题
例如有一个数据库basic 有很多个表（tbbook002.tbbook003,tbbook004,tbbook005,tbbook006,tbbook007,tbbook008,tbcustomer）
在tbcustomer表里有一个字段tbbook.如下
no name tbbook
001 ss tbbook002
002 dd tbbook003
003 eee tbbook004

.. .. ..
我想写一条语句。根据tbcustomer表的tbbook字段来删除数据库里的表。也就是说tbbook里没有记录就是要删除的表
就上面的例子来看。我要删除的表有tbbook005,tbbook006,tbbook007,tbbook008.



[解决办法]

SQL code

SELECT 'DELETE FROM '+'['+NAME+'];'FROM SYSOBJECTS WHERE XTYPE='U' AND NAME LIKE 'TBBOOK%' AND NAME NOT IN(SELECT TBBOOK FROM tbcustomer WHERE TBBOOK  IS NOT NULL)
[解决办法]
SQL code
declare   @tablename   varchar(100)     declare   @sql   varchar(4000)     declare   @Num   int     declare   tb_cursor   scroll   cursor   for   select   name   from   sysobjects   where   xtype='U'     AND NAME LIKE 'TBBOOK%' AND NAME NOT IN(SELECT TBBOOK FROM tbcustomer WHERE TBBOOK  IS NOT NULL)            open   tb_cursor                 fetch   next   from   tb_cursor   into   @tablename               while   @@fetch_status=0                 begin                         set   @sql=' delete   from  '+@tablename                         exec (@sql)                    fetch   next   from   tb_cursor   into   @tablename               end                 close   tb_cursor                 deallocate   tb_cursor
[解决办法]
探讨

SQL codeSELECT'DELETE FROM'+'['+NAME+'];'FROM SYSOBJECTSWHERE XTYPE='U'AND NAMELIKE'TBBOOK%'AND NAMENOTIN(SELECT TBBOOKFROM tbcustomerWHERE TBBOOKISNOTNULL)

然后复制粘贴执行