向现有列添加标识

基本上有四个逻辑步骤。

1. 创建一个新的Identity列。为这个新列打开Insert Identity。

2. 将源列(希望转换为Identity的列)中的数据插入到这个新列中。

3. 关闭新列的Insert Identity。

4. 删除源列&将新列重命名为源列的名称。

可能会有一些更复杂的事情，比如跨多个服务器工作等。

有关步骤，请参阅下面的文章(使用ssms &t - sql)。这些步骤适用于不太熟悉T-SQL的初学者。

http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx

为所有没有标识集的主键= bigint的表生成脚本;这将返回每个表的生成脚本列表;

SET NOCOUNT ON;


declare @sql table(s varchar(max), id int identity)


DECLARE @table_name nvarchar(max),
@table_schema nvarchar(max);


DECLARE vendor_cursor CURSOR FOR
SELECT
t.name, s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE EXISTS (
SELECT
[c].[name]
from sys.columns [c]
join sys.types [y] on [y].system_type_id = [c].system_type_id
where [c].[object_id] = [t].[object_id] and [y].name = 'bigint' and [c].[column_id] = 1
) and NOT EXISTS
(
SELECT 1 FROM sys.identity_columns
WHERE [object_id] = t.[object_id]
) and exists (
select 1 from sys.indexes as [i]
inner join sys.index_columns as [ic]  ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
where object_name([ic].[object_id]) = [t].[name]
)
OPEN vendor_cursor


FETCH NEXT FROM vendor_cursor
INTO @table_name, @table_schema


WHILE @@FETCH_STATUS = 0
BEGIN


DELETE FROM @sql


declare @pkname varchar(100),
@pkcol nvarchar(100)


SELECT  top 1
@pkname = i.name,
@pkcol = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM    sys.indexes AS [i]
INNER JOIN sys.index_columns AS [ic] ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) = @table_name


declare @q nvarchar(max) = 'SELECT  '+@pkcol+' FROM ['+@table_schema+'].['+@table_name+'] ORDER BY '+@pkcol+' DESC'


DECLARE @ident_seed nvarchar(max) -- Change this to the datatype that you are after
SET @q = REPLACE(@q, 'SELECT', 'SELECT TOP 1 @output = ')
EXEC sp_executeSql @q, N'@output bigint OUTPUT', @ident_seed OUTPUT


insert into  @sql(s) values ('BEGIN TRANSACTION')
insert into  @sql(s) values ('BEGIN TRY')


-- create statement
insert into  @sql(s) values ('create table ['+@table_schema+'].[' + @table_name + '_Temp] (')


-- column list
insert into @sql(s)
select
'  ['+[c].[name]+'] ' +
y.name +


(case when [y].[name] like '%varchar' then
coalesce('('+(case when ([c].[max_length] < 0 or [c].[max_length] >= 1024) then 'max' else cast([c].max_length as varchar) end)+')','')
else '' end)


+ ' ' +
case when [c].name = @pkcol then 'IDENTITY(' +COALESCE(@ident_seed, '1')+',1)' else '' end + ' ' +
( case when c.is_nullable = 0 then 'NOT ' else '' end ) + 'NULL ' +
coalesce('DEFAULT ('+(
REPLACE(
REPLACE(
LTrim(
RTrim(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
LTrim(
RTrim(
REPLACE(
REPLACE(
object_definition([c].default_object_id)
,' ','~')
,')',' ')
)
)
,' ','*')
,'~',' ')
,' ','~')
,'(',' ')
)
)
,' ','*')
,'~',' ')
) +
case when object_definition([c].default_object_id) like '%get%date%' then '()' else '' end
+
')','') + ','
from sys.columns c
JOIN sys.types y ON y.system_type_id = c.system_type_id
where OBJECT_NAME(c.[object_id]) = @table_name and [y].name != 'sysname'
order by [c].column_id




update @sql set s=left(s,len(s)-1) where id=@@identity


-- closing bracket
insert into @sql(s) values( ')' )


insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] ON')


declare @cols nvarchar(max)
SELECT @cols = STUFF(
(
select ',['+c.name+']'
from sys.columns c
JOIN sys.types y ON y.system_type_id = c.system_type_id
where c.[object_id] = OBJECT_ID(@table_name)
and [y].name != 'sysname'
and [y].name != 'timestamp'
order by [c].column_id
FOR XML PATH ('')
)
, 1, 1, '')


insert into @sql(s) values( 'IF EXISTS(SELECT * FROM ['+@table_schema+'].['+@table_name+'])')
insert into @sql(s) values( 'EXEC(''INSERT INTO ['+@table_schema+'].['+@table_name+'_Temp] ('+@cols+')')
insert into @sql(s) values( 'SELECT '+@cols+' FROM ['+@table_schema+'].['+@table_name+']'')')


insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] OFF')




insert into @sql(s) values( 'DROP TABLE ['+@table_schema+'].['+@table_name+']')


insert into @sql(s) values( 'EXECUTE sp_rename N''['+@table_schema+'].['+@table_name+'_Temp]'', N'''+@table_name+''', ''OBJECT''')


if ( @pkname is not null ) begin
insert into @sql(s) values('ALTER TABLE ['+@table_schema+'].['+@table_name+'] ADD CONSTRAINT ['+@pkname+'] PRIMARY KEY CLUSTERED (')
insert into @sql(s)
select '  ['+COLUMN_NAME+'] ASC,' from information_schema.key_column_usage
where constraint_name = @pkname
GROUP BY COLUMN_NAME, ordinal_position
order by ordinal_position


-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
insert into @sql(s) values ('  )')
end


insert into  @sql(s) values ('--Run your Statements')
insert into  @sql(s) values ('COMMIT TRANSACTION')
insert into  @sql(s) values ('END TRY')
insert into  @sql(s) values ('BEGIN CATCH')
insert into  @sql(s) values ('        ROLLBACK TRANSACTION')
insert into  @sql(s) values ('        DECLARE @Msg NVARCHAR(MAX)  ')
insert into  @sql(s) values ('        SELECT @Msg=ERROR_MESSAGE() ')
insert into  @sql(s) values ('        RAISERROR(''Error Occured: %s'', 20, 101,@msg) WITH LOG')
insert into  @sql(s) values ('END CATCH')


declare @fqry nvarchar(max)


-- result!
SELECT @fqry = (select char(10) + s from @sql order by id FOR XML PATH (''))




SELECT @table_name as [Table_Name], @fqry as [Generated_Query]
PRINT 'Table: '+@table_name
EXEC sp_executeSql @fqry


FETCH NEXT FROM vendor_cursor
INTO @table_name, @table_schema
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

简单的解释

使用sp_RENAME重命名现有列

EXEC sp_RENAME 'Table_Name。Existing_ColumnName'， 'New_ColumnName'， 'COLUMN'

重命名示例:

现有列UserID被重命名为OldUserID

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

然后使用alter query添加一个新列来设置为主键和标识值

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

使用实例设置主键

新创建的列名为UserID

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

然后删除重命名列

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

删除重命名列的示例

ALTER TABLE Users DROP COLUMN OldUserID

现在，我们向表上的现有列添加了一个主键和标识。

考虑使用序列代替恒等式。

在sql server 2014(我不知道低版本)，你可以简单地使用序列。

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;


ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

从这里:序列作为列的默认值

现在从'Employee'表中删除'EmployeeId'列
ALTER TABLE Employee DROP COLUMN EmployeeId

2. 这将抛出错误，因为主键约束规则适用并验证列结构。
   * # # # “# EYZ0 # # # < / p > < /李> 所以我们必须首先从表'Employee'中删除主键约束，然后我们可以删除列
   ALTER TABLE Employee DROP constraint [PK_dbo.]员工)< /强> < / p >

现在，我们可以从'Employee'表中删除'EmployeeId'列，就像在我们得到错误的前一步中所做的那样
ALTER TABLE Employee DROP COLUMN EmployeeId

3. 现在列'EmployeeId'从表中删除 因此，我们将新添加的列EmployeeId_new重命名为EmployeeId
   sp_rename”员工。EmployeeId'， 'EmployeeId_new'， 'COLUMN'

要以相同的形式重新排列表，我们必须为'EmployeeId'列添加主键约束。
ALTER TABLE员工表Employee]主键(EmployeeId)
. log