在所有表中，如果不存在，则添加一列？

48 浏览
0 Comments

在所有表中，如果不存在，则添加一列？

我正在使用SQL Server 2005/2008。我需要在表中添加列，如果它还不存在。这将适用于给定数据库中的所有表。我希望我已经接近了解决方案，但我在此解决方案中遇到了问题。

怎样才能完成此操作？

这是我所拥有的：

EXEC sp_MSforeachtable '
    declare @tblname varchar(255);
    SET @tblname =  PARSENAME("?",1);
    if not exists (select column_name from INFORMATION_SCHEMA.columns 
                   where table_name = @tblname and column_name = ''CreatedOn'') 
    begin
        ALTER TABLE @tblname ADD CreatedOn datetime NOT NULL DEFAULT getdate();
    end
'

但是我收到了错误信息：

错误102：在 \'@tblname\' 附近存在语法错误。

\'CreatedOn\' 附近存在语法错误。

\'@tblname\' 附近存在语法错误。

\'CreatedOn\' 附近存在语法错误。

... 对于每个表都是如此。

admin 更改状态以发布 2023年5月23日
0

2 答案

0 Comments

你需要加入一些动态SQL。这应该可以工作：

EXEC sp_MSforeachtable '
    declare @tblname varchar(255);
    SET @tblname =  PARSENAME("?",1);
    declare @sql nvarchar(1000);
    if not exists (select column_name from INFORMATION_SCHEMA.columns 
                   where table_name = @tblname and column_name = ''CreatedOn'') 
    begin
        set @sql = N''ALTER TABLE '' +  @tblname + N'' ADD CreatedOn datetime NOT NULL DEFAULT getdate();''
        exec sp_executesql @sql
    end
'

0
0 Comments

你不能在DDL中使用像@tableName这样的变量。此外，将名称分成部分并忽略模式只会导致错误。您应该在SQL批处理参数中使用“？”替换，并依赖于MSforeachtable替换：

EXEC sp_MSforeachtable '
if not exists (select * from sys.columns 
               where object_id = object_id(''?'')
               and name = ''CreatedOn'') 
begin
    ALTER TABLE ? ADD CreatedOn datetime NOT NULL DEFAULT getdate();
end';

0