Sql Practice 2

之前写了一个SP用来向dimention table插入0 -1 dummy row的值,但今天在process adventureworksdw2008示例

数据库的时候报错,查看了一下,是因为自己当时实验时插入的数据有问题,就想清除掉这些数据.

本想使用sp_Msforeachtable 但一直报错 不能识别$identity.

exec sp_MSforeachtable @command1="delete from '?'"

,@whereand='and $identity < 1'

 

只好写下了如下的代码:

declare @tables table(tablename varchar(200))

declare @tablename varchar(200)

declare @sql nvarchar(2000)

insert into @tables

select name from sys.tables where name like 'Dim%'

while exists(select * from @tables)

begin

select top 1 @tablename = tablename from @tables

delete from @tables where tablename =@tablename

set @sql = N'delete from ' + @tablename + ' where $identity < 1'

print @sql

execute sp_executesql @sql

end