27,582
社区成员




--0. 定义表变量
DECLARE @t TABLE (
rowNum INT IDENTITY(1,1) PRIMARY KEY,
tableName NVARCHAR(100)
)
--1. 将所有包含 order 而且包含 statu 字段的表名插入到表变量
INSERT INTO @t(tableName)
SELECT t.name FROM sys.tables AS t WHERE EXISTS (
SELECT * FROM sys.[columns] AS c
WHERE c.[object_id]=t.[object_id]
AND c.name='order'
)
AND EXISTS (
SELECT * FROM sys.[columns] AS c
WHERE c.[object_id]=t.[object_id]
AND c.name='statu'
)
--2. 遍历表变量中的所有表,构造动态SQL,更新相关记录
DECLARE @i INT,@iMax INT,@sql NVARCHAR(MAX)
SELECT @i=1,@iMax=MAX(rowNum) FROM @t
WHILE @i<=@iMax
BEGIN
SELECT @sql='update '+t.tableName+' set [statu]=4 where order=3' FROM @t t
PRINT @sql
EXEC (@sql)
SET @i=@i+1
END
--把这三个表的order==4的数据的字段statu的值改成4
UPDATE A SET statu=4
WHERE order=4
UPDATE B SET statu=4
WHERE order=4
UPDATE C SET statu=4
WHERE order=4