ALTER COLUMN & ldquo; IDENTITY & rdquo; для SQL Server [дубликат]

Чтобы изменить свойства идентификации для столбца:

• В «Проводнике сервера» щелкните правой кнопкой мыши таблицу со свойствами идентификации, которые вы хотите изменить, и нажмите «Открыть таблицу определения». Открывается таблица в конструкторе таблиц.
• Установите флажок Разрешить nulls для столбца, который вы хотите изменить.
• На вкладке «Свойства столбца» разверните свойство «Спецификация удостоверений».
• Щелкните ячейку сетки для дочернего свойства Is Identity и выберите Да из раскрывающегося списка.
• Введите значение в ячейке Identity Seed. Это значение будет присвоено первой строке в таблице. Значение по умолчанию будет присвоено.

Вот и все, что сработало для меня

генерирует скрипт для всех таблиц с первичным ключом = 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;

Вы не можете изменить столбец, который будет столбцом IDENTITY. Что вам нужно сделать, это создать новый столбец, который определяется как идентификатор из get-go, затем удалить старый столбец и переименовать новый в старое имя.

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

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

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

Марк

По дизайну нет простого способа включить или отключить функцию идентификации для существующего столбца. Единственный чистый способ сделать это - создать новый столбец и сделать его столбцом идентификации или создать новую таблицу и перенести ваши данные.

Если мы используем SQL Server Management Studio, чтобы избавиться от значения идентификатора в столбце «id» создается новая временная таблица, данные перемещаются во временную таблицу, старая таблица удаляется и новая таблица переименовывается.

Используйте Management Studio, чтобы внести изменения, а затем щелкните правой кнопкой мыши в конструкторе и выберите «Сгенерировать сценарий изменений».

Вы увидите, что это то, что делает SQL-сервер в фоновом режиме.

Простое объяснение

Переименуйте существующий столбец, используя sp_RENAME

EXEC sp_RENAME 'Table_Name.Existing_ColumnName', 'New_ColumnName', 'COLUMN'

Пример для переименования :

Существующий столбец UserID переименован в OldUserID

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

Затем добавьте новый столбец, используя альтернативный запрос, чтобы установить как первичный ключ и значение удостоверения

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

Пример для Set Primary key

Новое имя созданного столбца - UserID

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

, затем Drop the Renamed Column

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

Пример для Drop переименован столбец

ALTER TABLE Users DROP COLUMN OldUserID

Теперь мы добавили ключ доступа и идентификатор к существующему столбцу таблицы.

В принципе существует четыре логических шага.

1. Создайте новый столбец Identity. Включите Insert Identity для этого нового столбца.
2. Вставьте данные из столбца источника (столбец, который вы хотите преобразовать в Identity) в этот новый столбец.
3. Отключите Insert Identity для нового столбца.
4. Оставьте исходный столбец & amp; переименуйте новый столбец в имя исходного столбца.

Могут быть некоторые сложности, такие как работа на нескольких серверах и т. д.

Пожалуйста, обратитесь к следующей статье для шаги (используя ssms & amp; T-sql). Эти шаги предназначены для новичков с меньшим сцеплением с T-SQL.

http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert- INT-столбца к идентичности-в-MS-SQL-server.aspx

вы не можете этого сделать, вам нужно добавить другой столбец, удалить исходный столбец и переименовать новый столбец или создать новую таблицу, скопировать данные и удалить старую таблицу, а затем переименовать новую таблицу к старой таблице

, если вы используете SSMS и устанавливаете свойство identity в ON в конструкторе, это то, что SQL Server делает за кулисами. Поэтому, если у вас есть таблица с именем [user], это происходит, если вы делаете UserID и identity

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION

GO

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

. Сказав, что есть способ взломать системную таблицу, чтобы выполнить ее, установив побитовое значение но это не поддерживается, и я бы этого не сделал

Щелкните правой кнопкой мыши на имени таблицы в Обозревателе объектов. Вы получите некоторые варианты. Нажмите «Дизайн». Для этой таблицы будет открыта новая вкладка. Вы можете добавить ограничение Identity здесь в «Свойства столбца».

Я не верю, что вы можете изменить существующий столбец как столбец идентификатора, используя tsql. Тем не менее, вы можете сделать это через представление дизайна Enterprise Manager.

Альтернативно вы можете создать новую строку в качестве столбца идентификации, удалить старый столбец, а затем переименовать новый столбец.

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