环境:SQL Server 2008
由于增加了新功能,需要对原来的Database中所有的表加一个相同的字段,用来表示某记录的某种状态。而这个database中的表非常多,一个个去加,就显得很笨了,是吧?查了半天,终于用SQL写了一个循环,将这个功能实现了。
1. 首先我们需要将某个表加一个字段
这个字段名为Lived,对应SQL语句如下:
alter table tablename add [columnname] int
上面的tablename换成自己的表名,columnname换成自己的字段名即可。用中括号括上是为了避免和SQL Server的某些保留字冲突。
2. 在表中加上此字段后,我们需要将原有数据的Lived字段设为1
对应SQL 语句如下:
update tablename set [columname]=1
3.如何取出某个数据库中所有表名呢?
当然仅限于我们自己创建的表,而不是系统表.用下面SQL语句就可以了.
select name from sysobjects where xtype='u'
感兴趣的朋友可以用select
4.接着就是SQL语句如何循环呢?
现在我们已经可以取到某个database中所有用户表 ,如何循环对每个表进行操作呢?
declare tnames_cursor CURSOR
FOR
select name from sysobjects where xtype='u'
OPEN tnames_cursor
DECLARE @table sysname
FETCH NEXT FROM tnames_cursor INTO @table
while(@@FETCH_STATUS <> -1)
begin
IF(@@FETCH_STATUS <> -2)
BEGIN
--在这里写上你对每个表的操作
END
FETCH NEXT FROM tnames_cursor INTO @table
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
返回被
0
-1
-2
5. 现在就剩最后一个问题了。如何在循环中执行SQL语句呢?
很简单,用exec就可以。如下:
EXEC ('alter table ' + @table + ' add [Lived] int ')
EXEC ('Update ' + @table + ' set [Lived]=1 ')
上面的[Lived]即为字段名。
到现在为止,就基本完成了我们想要的效果。
循环遍历某个database,对每个表进行添加[Lived]字段的操作,并将此表的所有记录的Lived初始化为1。完整代码如下:
use databasename
go
declare tnames_cursor CURSOR
FOR
select name from sysobjects where xtype='u'
OPEN tnames_cursor
DECLARE @table sysname
FETCH NEXT FROM tnames_cursor INTO @table
while(@@FETCH_STATUS <> -1)
begin
IF(@@FETCH_STATUS <> -2)
BEGIN
EXEC ('alter table ' + @table + ' add [Lived] int ')
EXEC ('Update ' + @table + ' set [Lived]=1 ')
print 'alter table ' + @table + ' add [Lived] int '
END
FETCH NEXT FROM tnames_cursor INTO @table
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
现在需求变了一点点。循环遍历某个Database,取出每个表的记录数,如果记录数为1,就打印出这个表名。
这里高级一点点的功能就是:执行select count(*)语句后,需要吧它记录在某个地方。下面先看完整代码:
use databasename
go
declare tnames_cursor CURSOR
FOR
select name from sysobjects where xtype='u'
OPEN tnames_cursor
DECLARE @table sysname
DECLARE @num int, @sqls nvarchar(4000)
FETCH NEXT FROM tnames_cursor INTO @table
while(@@FETCH_STATUS <> -1)
begin
IF(@@FETCH_STATUS <> -2)
BEGIN
set @sqls = 'select @a=count(*) FROM ' + @table
exec sp_executesql @sqls, N'@a int output', @num output
if( @num = 1)
BEGIN
print @table
END
END
FETCH NEXT FROM tnames_cursor INTO @table
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
这种情况下不能用EXEC()
要用 sp_executesql才行,因为变量的生存期只在sql动态语句中,出了就无效了.用sp_executesql可以解决这个问题.因为可以传参数,可是EXEC()不能够传参数。