一次更改多个表 [英] Altering Multiple Tables at once

问题描述

我正在尝试一次更改多个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:

查找错误-SQL Server数据库错误：名称'ALTER TABLE
dbo.PM1GTVLV ADD [identityID] bigint IDENTITY（1、1）NOT NULL'不是
a有效标识符。

Lookup Error - SQL Server Database Error: The name 'ALTER TABLE dbo.PM1GTVLV ADD [identityID] bigint IDENTITY(1, 1) NOT NULL ' is not a valid identifier.

我还尝试了多种字符串变体，并使用了 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;

这篇关于一次更改多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！