select 'alter table '+name+' add A varchar(50)' from sysobjects t1 where t1.type='U' and t1.name like '%_ITM' and not exists(select 1 from syscolumns t2 where t2.id=t1.id and t2.name='A') 先执行这句,再执行其结果就可以了
USE DB GODECLARE @SQL VARCHAR(8000) SET @SQL='' SELECT @SQL=@SQL+'ALTER TABLE '+C.[TABLE]+' ADD A VARCHAR(10);' FROM ( SELECT DISTINCT B.NAME AS [TABLE] FROM SYS.ALL_COLUMNS AS A INNER JOIN SYS.TABLES AS B ON A.OBJECT_ID=B.OBJECT_ID WHERE B.NAME LIKE '%_ITM' AND A.OBJECT_ID NOT IN (SELECT OBJECT_ID FROM SYS.ALL_COLUMNS WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM SYS.TABLES) AND NAME='A')) AS C EXEC(@SQL)
结合系统表sysobjects和syscolumns
看下你就懂
select t1.name
from sysobjects t1,sysobjects t2
where t1.id=t2.id
and t1.type='U'
and t1.name like '%_ITM'
and t2.name <> 'A'
syscolumns 这里表列的信息判断一下 表的列是否已存在?决定是否创建
from sysobjects t1
where t1.type='U' and t1.name like '%_ITM'
and not exists(select 1 from syscolumns t2 where t2.id=t1.id and t2.name='A')
先执行这句,再执行其结果就可以了
USE DB
GODECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+'ALTER TABLE '+C.[TABLE]+' ADD A VARCHAR(10);' FROM (
SELECT DISTINCT B.NAME AS [TABLE] FROM SYS.ALL_COLUMNS AS A INNER JOIN SYS.TABLES AS B ON A.OBJECT_ID=B.OBJECT_ID
WHERE B.NAME LIKE '%_ITM' AND A.OBJECT_ID NOT IN (SELECT OBJECT_ID FROM SYS.ALL_COLUMNS
WHERE OBJECT_ID IN (
SELECT OBJECT_ID FROM SYS.TABLES) AND NAME='A')) AS C
EXEC(@SQL)