我是一名java开发人员，碰巧加入了一个没有DBA的团队，而作为一名开发人员，我无法获得DBA权限。我的任务是在两个数据库之间移动整个模式，所以没有DBA，我必须通过运行脚本来完成，不能使用SQL Server 2008中的GUI，因为我没有管理权限。

但是，在新模式上运行存储过程时，所有内容都被移动了。表，我发现我丢失了一个表的标识字段。我仔细检查了创建表的脚本，它在那里，但是，SQL Server在我运行脚本时没有得到它。后来，一位DBA告诉我，他以前见过同样的问题。

在任何情况下，对于SQL Server 2008，这些是我采取的步骤来解决这个问题，他们工作，所以我张贴在这里，希望它会对某人有所帮助。这是我所做的，因为我对另一个表有FK依赖，这使得这更困难:

我使用这个查询来验证标识确实缺失，并查看表上的依赖关系。

1)。查找表上的统计信息:

exec sp_help 'dbo.table_name_old';

2)。创建一个重复的、完全相同的新表，只是在原来的PK字段上添加了一个标识字段。

3)。禁用标识以移动数据。

SET IDENTITY_INSERT dbo.table_name ON 

4)。传输数据。

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT 
field1, field2, etc...
FROM 
dbo.table_name_old;

5)。验证数据是否存在。

SELECT * FROM dbo.table_name_new

6)。重新启用标识。

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7)。这是我找到的获得所有FK关系以验证原始表引用哪个表作为依赖项的最佳脚本 我遇到过很多，所以它是一个保存者!

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
   ORDER BY ReferenceTableName;

8)。在进行下一步之前，请确保您拥有所有涉及到的表的所有PK和FK脚本。

9)。您可以右键单击每个键并使用SQL Server 2008编写脚本

10)。使用以下语法从依赖表中删除FK:

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

11)。删除原来的表:

DROP TABLE dbo.table_name_old;

13)。接下来的步骤依赖于您在SQL Server 2008第9步中创建的脚本。

将PK添加到新表中。

——将FK添加到新表中。

——将FK添加回依赖项表。

14)。验证所有内容是否正确和完整。我使用GUI查看表格。

15)。将新表重命名为原始表名。

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';

最后，一切都成功了!

基本上有四个逻辑步骤。

创建一个新的Identity列。为这个新列打开Insert Identity。 将源列(希望转换为Identity的列)中的数据插入到这个新列中。 关闭新列的Insert Identity。 删除源列并将新列重命名为源列的名称。

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

有关步骤(使用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

不能将列更改为IDENTITY列。您需要做的是创建一个新列，从一开始就定义为IDENTITY，然后删除旧列，并将新列重命名为旧名称。

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc

您不能更改现有列的标识。

你有两个选择，

创建一个带有标识的新表并删除现有表 创建一个具有标识的新列并删除现有列

方法1。(新表)在这里，您可以保留新创建的标识列上的现有数据值。请注意，如果不满足'if not exists'，您将丢失所有数据，因此请确保您将条件也放在drop上!

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

方法2(新列)不能在新创建的标识列上保留现有数据值，标识列将保留数字序列。

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

请参阅以下Microsoft SQL Server论坛帖子了解更多细节:

如何将列更改为identity(1,1)

如果你碰巧使用Visual Studio 2017+

在服务器对象资源管理器中，右键单击表格，选择“视图代码” 向列添加修饰符“IDENTITY” 更新

这将为你做的一切。

为所有没有标识集的主键= 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;