本文介绍了一次更改多个表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试一次更改多个SQL Server 2008 R2表。
I'm trying to alter multiple SQL Server 2008 R2 tables at one time.
这是我的代码:
use DatabaseName
go
Declare @SchemaUsed varchar(20) = 'dbo'
create table #Tables
(
TableName varchar(100), Processed int
)
insert into #Tables
select top 1 table_name, 0
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = @SchemaUsed
and table_type = 'Base Table'
and (TABLE_NAME like 'PM%' )
ORDER BY TABLE_NAME
DECLARE @TableName varchar(max)
DECLARE @SQL varchar(max)
WHILE EXISTS (select top 1 'x' from #Tables where Processed = 0)
BEGIN
SET @TableName = (select top 1 TableName from #Tables where Processed = 0)
Set @SQL = 'ALTER TABLE ' + @SchemaUsed + '.' + @TableName + ' ADD [identityID] bigint IDENTITY(1, 1) NOT NULL '
-- Set @SQL = '''' + @SQL + ''''
Print @SQL
EXEC @SQL;
update #Tables
set Processed = 1
where TableName = @TableName
END
drop table #Tables
我无法使用它来挽救生命并出现以下错误:
I can't get this to work to save my life and get the following error:
我还尝试了多种字符串变体,并使用了 sp_executesql
。
I've also tried multiple string variations and using sp_executesql
as well.
有人可以指出我错了吗?
Can someone point out where I've gone wrong?
推荐答案
尝试
DECLARE @SQL NVARCHAR(MAX);
EXEC sp_executesql @SQL;
代替EXEC @sql。
Instead of EXEC @sql.
顺便说一句,这是相同代码恕我直言的更有用的版本:
As an aside, this is a much more usable version of the same code IMHO:
DECLARE @SchemaUsed VARCHAR(20) = 'dbo';
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE '
+ QUOTENAME(@SchemaUsed) + '.'
+ QUOTENAME(name) + ' ADD [identityID]
BIGINT IDENTITY(1,1) NOT NULL;'
FROM sys.tables
WHERE SCHEMA_NAME([schema_id]) = @SchemaUsed
AND name LIKE 'PM%';
PRINT @sql;
--EXEC sp_executesql @sql;
甚至更好:
DECLARE @SchemaUsed VARCHAR(20) = 'dbo';
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE '
+ QUOTENAME(@SchemaUsed) + '.'
+ QUOTENAME(name) + ' ADD [identityID]
BIGINT IDENTITY(1,1) NOT NULL;'
FROM sys.tables AS t
WHERE SCHEMA_NAME([schema_id]) = @SchemaUsed
AND name LIKE 'PM%'
AND NOT EXISTS (SELECT 1 FROM sys.columns AS c
WHERE [object_id] = t.[object_id]
AND c.is_identity = 1);
PRINT @sql;
--EXEC sp_executesql @sql;
这篇关于一次更改多个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!