是否可以同时向多个表添加列？

Question

我正在使用SQL Server。我想向多个表中添加一个名为[DateCreated]的单独列。是否可能通过一条语句将此列添加到数据库中的所有表中？

我偶然发现了Joe Steffaneli的答案，他建议使用一个查询，该查询返回由Alter table语句组成的行。 查询如下：

select 'alter table ' + quotename(s.name) + '.' + quotename(t.name) + ' add [DateModified] datetime'
    from sys.columns c
        inner join sys.tables t
            on c.object_id = t.object_id
        inner join sys.schemas s
            on t.schema_id = s.schema_id
        left join sys.columns c2
            on t.object_id = c2.object_id
                and c2.name = 'DateModified'
    where c.name = 'DateCreated'
        and t.type = 'U'
        and c2.column_id is null /* DateModified column does not already exist */ 

有没有办法执行返回的行？对不起，我的英语不好。

- Bhaskar

---

请告知您使用的数据库管理系统，我们可以为您提供一批语句来实现相同的功能。 - RichardTheKiwi

Answer 1

你可能需要像这样的内容。在运行之前，请检查脚本是否实现了你想要的功能（添加一个非空列，其默认值为getdate()）！

DECLARE @Dynsql nvarchar(max) 
SET @Dynsql = ''

SELECT @Dynsql = @Dynsql + '
alter table ' + QUOTENAME(SCHEMA_NAME(schema_id))+ '.' + QUOTENAME(name)  + 
' add [DateCreated] datetime not null default getdate()' 
FROM sys.tables
WHERE type='U' and object_id NOT IN (select object_id from sys.columns where name='DateCreated')


EXEC (@Dynsql)

- Martin Smith

---

如何针对另一个数据库进行操作？我试过了但只有master被修改了，myTest 数据库没有变化。 - Bellash

我们是否应该在脚本末尾添加GO关键字呢？因为这将会是多个脚本的序列。 - Rohin Tak

Answer 2

您可以使用此查询。 在此查询中，我使用where子句（可选）来查找所有具有ID的表并向其中添加新字段。

您可以更改where子句，例如查找所有具有BusinessId列的表并添加新字段，或删除where子句并向所有表添加新字段。

DECLARE @SQL varchar(max)

SELECT @SQL= STUFF((SELECT ';' + 'ALTER TABLE ' + t.TABLE_SCHEMA+'.' +t.TABLE_NAME+ ' ADD newfield nvarchar(max)' 

from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name
                                and c.table_schema = t.table_schema
where c.column_name = 'id'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
      FOR XML PATH('')),1,1,'')


EXEC (@SQL)

- shayan kamalzadeh

Answer 3

这个存储过程运行良好

USE databaseName;
exec sp_msforeachtable 'alter table ? Add [DateCreated] datetime not null default getdate()';

- Bellash

Answer 4

declare @i int

set @i=1
while(@i<45)
begin
    declare @sql varchar(200)

    with TempTable as (select Row_number() over(order by stdID) as RowNo,* from SomeTable)

    select @sql= 'alter table Table'+(select Name from TempTable where RowNo=@i)+' add NewColumn int'

    exec (@sql)
    set @i=@i+1
end

- Ranjit Gani

Answer 5

不，没有一个单一的语句可以在你的数据库中添加一个列到所有的表格。

下次请标记你所使用的关系型数据库系统。如果有这样的方法，我们也需要知道你使用的是哪个数据库系统才能给你正确的命令。

- Dan Grossman