我是一名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属性属于表而不是列。

更简单的方法是在GUI中完成，但如果没有这个选项，则可以复制数据、删除列、用标识重新添加它，然后放回数据。

请看这里的详细说明。

考虑使用SEQUENCE而不是IDENTITY。

在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

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

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

我不相信您可以使用tsql将现有列更改为标识列。但是，您可以通过Enterprise Manager设计视图来完成。

或者，您可以创建一个新行作为标识列，删除旧列，然后重命名新列。

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED

修改列的标识属性:

In Server Explorer, right-click the table with identity properties you want to modify and click Open Table Definition. The table opens in Table Designer. Clear the Allow nulls check box for the column you want to change. In the Column Properties tab, expand the Identity Specification property. Click the grid cell for the Is Identity child property and choose Yes from the drop-down list. Type a value in the Identity Seed cell. This value will be assigned to the first row in the table. The value 1 will be assigned by default.

就是这样，对我很管用