SQL 如果所有表都不存在，则添加一列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5146070/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add a column if it doesn't exist to all tables?
提问by Scott Stafford
I'm using SQL Server 2005/2008. I need to add a column to a table if it does not yet exist. This will apply to all tables in a given database. I hoped I was close, but I'm having issues with this solution.
我正在使用 SQL Server 2005/2008。如果表尚不存在，我需要向表中添加一列。这将适用于给定数据库中的所有表。我希望我很接近，但我在这个解决方案上遇到了问题。
How can this be done?
如何才能做到这一点？
Here's what I have:
这是我所拥有的：
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
'
But I get errors:
但我收到错误：
Error 102: Incorrect syntax near '@tblname'. Incorrect syntax near 'CreatedOn'. Incorrect syntax near '@tblname'. Incorrect syntax near 'CreatedOn'. ... and so on, for each table.
错误 102：“@tblname”附近的语法不正确。“CreatedOn”附近的语法不正确。'@tblname' 附近的语法不正确。“CreatedOn”附近的语法不正确。... 依此类推，对于每张桌子。
采纳答案by Remus Rusanu
You cannot use variables, like @tableName, in DDL. Besides, splinting the name into part and ignoring the schema can only result in bugs. You should just use the ''?'' replacement in the SQL batch parameter and rely on the MSforeachtablereplacement:
您不能在 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';
回答by Joe Stefanelli
You'll need to mix in a bit of dynamic SQL. This should work:
您需要混合一些动态 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
'
回答by Ajeet Verma
DECLARE @Column VARCHAR(100) = 'Inserted_date'
DECLARE @sql VARCHAR(max) = NULL
SELECT @sql += ' ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + 'ADD' + @Column + 'datetime NOT NULL DEFAULT getdate()' + '; '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME IN (
SELECT DISTINCT NAME
FROM SYS.TABLES
WHERE type = 'U'
AND Object_id IN (
SELECT DISTINCT Object_id
FROM SYS.COLUMNS
WHERE NAME != @Column
)
)
EXEC Sp_executesql @sql
回答by Andriy M
Maybe like this:
也许是这样的：
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 [?] ADD CreatedOn datetime NOT NULL DEFAULT getdate();
end
'
?
?
Or even like this:
或者甚至像这样：
EXEC sp_MSforeachtable '
if not exists (select column_name from INFORMATION_SCHEMA.columns
where table_name = ''?'' and column_name = ''CreatedOn'')
begin
ALTER TABLE [?] ADD CreatedOn datetime NOT NULL DEFAULT getdate();
end
'
