DECLARE @tablename VARCHAR(100) DECLARE Cur CURSOR FOR SELECT table.name FROM sysobjects as table join syscolumns as col on table.id = col.id WHERE table.xtype = 'u' and col.name = 'a' OPEN Cur FETCH Cur INTO @tablename WHILE @@Fetch_Status=0 BEGIN Update @tablename set a = 'value' FETCH Cur INTO @tablename END CLOSE Cur DEALLOCATE Cur
不好意思,应该是DECLARE @tablename VARCHAR(100) DECLARE Cur CURSOR FOR SELECT [table].name FROM sysobjects as [table] join syscolumns as col on [table].id = col.id WHERE [table].xtype = 'u' and col.name = 'a' OPEN Cur FETCH Cur INTO @tablename WHILE @@Fetch_Status=0 BEGIN Update @tablename set a = 'value' FETCH Cur INTO @tablename END CLOSE Cur DEALLOCATE Cur
为什么提示我 必须输入表变量@tablename 呢?
1.可以另外建一个表,存储A字段(要求唯一),然后在其上面建个主键,然后在这50个表上加上外键,就可以进行级联更新(on update cascade) 2.用循环,循环table name 也可以做
DECLARE @tablename VARCHAR(100)
DECLARE Cur CURSOR
FOR
SELECT table.name
FROM sysobjects as table join syscolumns as col on table.id = col.id
WHERE table.xtype = 'u' and col.name = 'a'
OPEN Cur
FETCH Cur INTO @tablename
WHILE @@Fetch_Status=0
BEGIN
Update @tablename set a = 'value'
FETCH Cur INTO @tablename
END
CLOSE Cur
DEALLOCATE Cur
DECLARE Cur CURSOR
FOR
SELECT [table].name
FROM sysobjects as [table] join syscolumns as col on [table].id = col.id
WHERE [table].xtype = 'u' and col.name = 'a'
OPEN Cur
FETCH Cur INTO @tablename
WHILE @@Fetch_Status=0
BEGIN
Update @tablename set a = 'value'
FETCH Cur INTO @tablename
END
CLOSE Cur
DEALLOCATE Cur
2.用循环,循环table name 也可以做