Удалить удостоверение из столбца в таблице

Question

Удалить удостоверение из столбца в таблице

У нас есть таблица 5 ГБ (около 500 миллионов строк), и мы хотим удалить свойство удостоверения в одном из столбцов, но когда мы пытаемся сделать это через SSMS - это истекает.

Можно ли это сделать через T-SQL?

162

sql sql-server t-sql

Источник

user963893 22 ноя '11 в 16:34

13 ответов

Решение

Если вы хотите сделать это без добавления и заполнения нового столбца, без переупорядочения столбцов и практически без простоев, поскольку данные в таблице не изменяются, давайте немного поработаем с функциональностью разделения (но поскольку разделы не используются, вы не т выпуск Enterprise Edition):

Удалите все внешние ключи, которые указывают на эту таблицу
Сценарий таблицы, которая будет создана; переименуйте все, например "MyTable2", "MyIndex2" и т. д. Удалите спецификацию "IDENTITY".
Теперь у вас должно быть две "одинаковые" таблицы, одна полная, другая пустая без ИДЕНТИЧНОСТИ.
Бежать ALTER TABLE [Original] SWITCH TO [Original2]
Теперь ваша исходная таблица будет пустой, а новая будет содержать данные. Вы переключили метаданные для двух таблиц (мгновенно).
Оставьте оригинал (теперь пустую таблицу), exec sys.sp_rename переименовать различные объекты схемы обратно в исходные имена, а затем вы можете воссоздать ваши внешние ключи.

Например, учитывая:

CREATE TABLE Original
(
  Id INT IDENTITY PRIMARY KEY
, Value NVARCHAR(300)
);
CREATE NONCLUSTERED INDEX IX_Original_Value ON Original (Value);

INSERT INTO Original
SELECT 'abcd'
UNION ALL 
SELECT 'defg';

Вы можете сделать следующее:

--create new table with no IDENTITY
CREATE TABLE Original2
(
  Id INT PRIMARY KEY
, Value NVARCHAR(300)
);
CREATE NONCLUSTERED INDEX IX_Original_Value2 ON Original2 (Value);

--data before switch
SELECT 'Original', *
FROM Original
UNION ALL
SELECT 'Original2', *
FROM Original2;

ALTER TABLE Original SWITCH TO Original2;

--data after switch
SELECT 'Original', *
FROM Original
UNION ALL
SELECT 'Original2', *
FROM Original2;

--clean up
DROP TABLE Original;
EXEC sys.sp_rename 'Original2.IX_Original_Value2', 'IX_Original_Value', 'INDEX';
EXEC sys.sp_rename 'Original2', 'Original', 'OBJECT';


UPDATE Original
SET Id = Id + 1;

SELECT *
FROM Original;

151

Источник

user155892 25 сен '14 в 21:24

Это путается с ограничениями внешнего и первичного ключей, поэтому вот несколько сценариев, которые помогут вам на вашем пути:

Сначала создайте дубликат столбца с временным именем:

alter table yourTable add tempId int NOT NULL default -1;
update yourTable set tempId = id;

Далее, получите имя вашего ограничения первичного ключа:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'yourTable';

Теперь попробуйте удалить ограничение первичного ключа для вашего столбца:

ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;

Если у вас есть внешние ключи, он потерпит неудачу, поэтому, если это так, отмените ограничения внешнего ключа. СОХРАНИТЕ ПОСЛЕ ТОГО, КАКИЕ ТАБЛИЦЫ ВЫ ЗАПУСКАЕТЕ, ПОЧЕМУ ВЫ МОЖЕТЕ ДОБАВИТЬ ОГРАНИЧЕНИЯ НАЗАД ПОЗЖЕ!!!

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'otherTable';
alter table otherTable drop constraint fk_otherTable_yourTable;
commit;
..

После того как все ограничения внешнего ключа будут удалены, вы сможете удалить ограничение PK, удалить этот столбец, переименовать временный столбец и добавить ограничение PK к этому столбцу:

ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;
alter table yourTable drop column id;
EXEC sp_rename 'yourTable.tempId', 'id', 'COLUMN';
ALTER TABLE yourTable ADD CONSTRAINT PK_yourTable_id PRIMARY KEY (id) 
commit;

Наконец, добавьте ограничения FK обратно в:

alter table otherTable add constraint fk_otherTable_yourTable foreign key (yourTable_id) references yourTable(id);
..

Эль Фин!

65

Источник

user508537 19 мар '13 в 14:15

У меня была такая же проблема. 4 оператора в SSMS вместо использования графического интерфейса, и это было очень быстро.

Сделать новый столбец
alter table users add newusernum int;
Копировать значения более
update users set newusernum=usernum;
Оставьте старую колонку
alter table users drop column usernum;
Переименуйте новый столбец в старое имя столбца
EXEC sp_RENAME 'users.newusernum' , 'usernum', 'COLUMN';

24

Источник

user684363 08 апр '15 в 21:04

Следующий скрипт удаляет поле Identity для столбца с именем "Id"

Надеюсь, поможет.

BEGIN TRAN
BEGIN TRY
    EXEC sp_rename '[SomeTable].[Id]', 'OldId';

    ALTER TABLE [SomeTable] ADD Id int NULL

    EXEC ('UPDATE [SomeTable] SET Id = OldId')

    ALTER TABLE [SomeTable] NOCHECK CONSTRAINT ALL

    ALTER TABLE [SomeTable] DROP CONSTRAINT [PK_constraintName];
    ALTER TABLE [SomeTable] DROP COLUMN OldId
    ALTER TABLE [SomeTable] ALTER COLUMN [Id] INTEGER NOT NULL
    ALTER TABLE [SomeTable] ADD CONSTRAINT PK_JobInfo PRIMARY KEY (Id)

    ALTER TABLE [SomeTable] CHECK CONSTRAINT ALL

    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN   
    SELECT ERROR_MESSAGE ()
END CATCH

14

Источник

user3094668 31 авг '15 в 14:41

В SQL Server вы можете включать и отключать вставку идентификатора следующим образом:

УСТАНОВИТЬ IDENTITY_INSERT имя_таблицы ВКЛ.

- запустите свои запросы здесь

ВЫКЛЮЧИТЬ IDENTITY_INSERT имя_таблицы

5

Источник

user2624918 31 янв '20 в 20:40

Сильфонный код работает нормально, когда мы не знаем имя столбца идентификатора.

и хотите скопировать данные в новую временную таблицу, такую как Invoice_DELETED. и в следующий раз мы используем: вставить в Invoice_DELETED выберите * из счета, где...

SELECT t1.*
INTO Invoice_DELETED
FROM Invoice t1
LEFT JOIN Invoice ON 1 = 0
--WHERE t1.InvoiceID = @InvoiceID

Особая благодарность Андрею М

для более подробного объяснения см.: /questions/2187230/pomestite-protsess-v-pesochnitsu-gde-on-mozhet-nanesti-naimenshij-vred/2187245#2187245

5

Источник

user2155778 26 июл '16 в 10:17

ALTER TABLE tablename add newcolumn int
update tablename set newcolumn=existingcolumnname
ALTER TABLE tablename DROP COLUMN existingcolumnname;
EXEC sp_RENAME 'tablename.oldcolumn' , 'newcolumnname', 'COLUMN'

Однако приведенный выше код работает, только если нет отношения первичного-внешнего ключа

3

Источник

user2169777 27 сен '18 в 07:10

ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME DROP IDENTITY);

1

Источник

user4197325 15 сен '20 в 12:03

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

=============================

            /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
            BEGIN TRANSACTION

            SET QUOTED_IDENTIFIER ON
            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
            ALTER TABLE dbo.SI_Provider_Profile
                DROP CONSTRAINT DF_SI_Provider_Profile_SIdtDateTimeStamp
            GO
            ALTER TABLE dbo.SI_Provider_Profile
                DROP CONSTRAINT DF_SI_Provider_Profile_SIbHotelPreLoaded
            GO
            CREATE TABLE dbo.Tmp_SI_Provider_Profile
                (
                SI_lProvider_Profile_ID int NOT NULL,
                SI_lSerko_Integrator_Token_ID int NOT NULL,
                SI_sSerko_Integrator_Provider varchar(50) NOT NULL,
                SI_sSerko_Integrator_Profile varchar(50) NOT NULL,
                SI_dtDate_Time_Stamp datetime NOT NULL,
                SI_lProvider_ID int NULL,
                SI_sDisplay_Name varchar(10) NULL,
                SI_lPurchased_From int NULL,
                SI_sProvider_UniqueID varchar(255) NULL,
                SI_bHotel_Pre_Loaded bit NOT NULL,
                SI_sSiteName varchar(255) NULL
                )  ON [PRIMARY]
            GO
            ALTER TABLE dbo.Tmp_SI_Provider_Profile SET (LOCK_ESCALATION = TABLE)
            GO
            ALTER TABLE dbo.Tmp_SI_Provider_Profile ADD CONSTRAINT
                DF_SI_Provider_Profile_SIdtDateTimeStamp DEFAULT (getdate()) FOR SI_dtDate_Time_Stamp
            GO
            ALTER TABLE dbo.Tmp_SI_Provider_Profile ADD CONSTRAINT
                DF_SI_Provider_Profile_SIbHotelPreLoaded DEFAULT ((0)) FOR SI_bHotel_Pre_Loaded
            GO
            IF EXISTS(SELECT * FROM dbo.SI_Provider_Profile)
                 EXEC('INSERT INTO dbo.Tmp_SI_Provider_Profile (SI_lProvider_Profile_ID, SI_lSerko_Integrator_Token_ID, SI_sSerko_Integrator_Provider, SI_sSerko_Integrator_Profile, SI_dtDate_Time_Stamp, SI_lProvider_ID, SI_sDisplay_Name, SI_lPurchased_From, SI_sProvider_UniqueID, SI_bHotel_Pre_Loaded, SI_sSiteName)
                    SELECT SI_lProvider_Profile_ID, SI_lSerko_Integrator_Token_ID, SI_sSerko_Integrator_Provider, SI_sSerko_Integrator_Profile, SI_dtDate_Time_Stamp, SI_lProvider_ID, SI_sDisplay_Name, SI_lPurchased_From, SI_sProvider_UniqueID, SI_bHotel_Pre_Loaded, SI_sSiteName FROM dbo.SI_Provider_Profile WITH (HOLDLOCK TABLOCKX)')
            GO

            -- Rename the primary key constraint or unique key In SQL Server constraints such as primary keys or foreign keys are objects in their own right, even though they are dependent upon the "containing" table.
            EXEC sp_rename 'dbo.SI_Provider_Profile.PK_SI_Provider_Profile', 'PK_SI_Provider_Profile_Old';
            GO
            -- backup old table in case of 
            EXECUTE sp_rename N'dbo.SI_Provider_Profile', N'SI_Provider_Profile_Old', 'OBJECT'
            GO

            EXECUTE sp_rename N'dbo.Tmp_SI_Provider_Profile', N'SI_Provider_Profile', 'OBJECT'
            GO

            ALTER TABLE dbo.SI_Provider_Profile ADD CONSTRAINT
                PK_SI_Provider_Profile PRIMARY KEY NONCLUSTERED 
                (
                SI_lProvider_Profile_ID
                ) WITH( PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

            GO
            COMMIT TRANSACTION

=============================

1

Источник

user5484919 21 май '18 в 09:34

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

Предположим, у вас есть таблица с двумя столбцами

ID Identity (1,1) | Name Varchar

и хотите вставить строку с идентификатором = 4. Таким образом, вы переустановили его на 3, так что следующий 4

DBCC CHECKIDENT([YourTable], RESEED, 3)

Сделать вставку

INSERT  INTO [YourTable]
        ( Name )
VALUES  ( 'Client' )

И вернем ваше семя к самому высокому ID, допустим, 15

DBCC CHECKIDENT([YourTable], RESEED, 15)

Готово!

1

Источник

user9268529 01 мар '18 в 19:34

Далее в ответе "Марка Соула"

Например, у вас есть 3 таких таблицы с двумя внешними ключами.

ДДЛ:

      CREATE TABLE [Table_1](
    [id] [int] IDENTITY(10000,1) NOT NULL PRIMARY KEY,
    [Name] [varchar](10) NULL
    )
GO

CREATE UNIQUE NONCLUSTERED INDEX Table_1_Index   
    ON [Table_1] ([Name]);

CREATE TABLE [Table_2](
    [id] [int] NOT NULL FOREIGN KEY REFERENCES [Table_1]([id]) ON UPDATE CASCADE,
    [car] [varchar](10) NULL
    )
GO

CREATE TABLE [Table_3](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [varchar](10) NULL FOREIGN KEY REFERENCES [Table_1]([Name])
    )
GO

insert into [Table_1] Values('John'),('Mike')
insert into [Table_2] Values(10000, 'Benz'),(10000, 'Volvo'),(10001, 'BMW')
insert into [Table_3] Values('John')
GO

Если вы хотите удалить идентификатор из столбца «id» из таблицы «Table_1» со схемой «dbo», выполните этот запрос.

Запрос:

      Declare @Schema SysName = 'dbo'
Declare @TableName SysName = 'Table_1'
Declare @ColumnName SysName = 'id'

Declare @Table_Name SYSNAME = QUOTENAME(@Schema)+'.'+QUOTENAME(@TableName)
Declare @SQLCreateTable NVARCHAR(MAX)
Declare @SQLFK NVARCHAR(MAX)
Declare @SQL NVARCHAR(MAX)

--get foreign keys
;with fk_columns AS 
(
    select constraint_object_id, OBJECT_NAME(constraint_object_id) KeyName, OBJECT_NAME(fkc.parent_object_id) FKTable, OBJECT_NAME(fkc.referenced_object_id) PKTable,
    COL_NAME(fkc.parent_object_id, parent_column_id) FKColumn, COL_NAME(fkc.referenced_object_id, referenced_column_id) PKColumn,
    delete_referential_action, update_referential_action, is_not_trusted
    from sys.foreign_key_columns fkc
    JOIN sys.foreign_keys fk
    on fk.object_id = fkc.constraint_object_id
    WHERE fkc.referenced_object_id = OBJECT_ID(@Table_Name)
)
SELECT @SQLFK = ISNULL((SELECT (
        SELECT CHAR(13) +
             'ALTER TABLE ' + fk.FKTable + ' WITH' 
            + CASE WHEN fk.is_not_trusted = 1 
                THEN ' NOCHECK' 
                ELSE ' CHECK' 
              END + 
              ' ADD CONSTRAINT [' + fk.KeyName  + '] FOREIGN KEY(' 
              + STUFF((
                SELECT ', [' + k.FKColumn + ']'
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.constraint_object_id
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')' +
              ' REFERENCES '+@Table_Name+' ('
              + STUFF((
                SELECT ', [' + k.PKColumn + ']'
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.constraint_object_id
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')'
            + CASE 
                WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' 
                WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
                WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' 
                ELSE '' 
              END
            + CASE 
                WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
                WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
                WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'  
                ELSE '' 
              END 
            + CHAR(13) + 'ALTER TABLE ' + fk.FKTable + ' CHECK CONSTRAINT [' + fk.KeyName  + ']' + CHAR(13)
        FROM fk_columns fk WITH (NOWAIT)
        FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')

--drop foreign keys
;with fk_columns AS 
(
    select OBJECT_NAME(constraint_object_id) KeyName, OBJECT_NAME(fkc.parent_object_id) FKTable
    from sys.foreign_key_columns fkc
    JOIN sys.foreign_keys fk
    on fk.object_id = fkc.constraint_object_id
    WHERE fkc.referenced_object_id = OBJECT_ID(@Table_Name)
)
SELECT @SQL = ISNULL((SELECT (
        SELECT CHAR(13) +
             'ALTER TABLE ' + fk.FKTable +
              ' DROP CONSTRAINT [' + fk.KeyName  + ']' 
            + CHAR(13)
        FROM fk_columns fk WITH (NOWAIT)
        FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
EXECUTE sp_executesql @SQL

--find not exist table name
Declare @i int = 1
WHILE OBJECT_ID(QUOTENAME(@Schema) + '.' +QUOTENAME(@TableName) + '_Temp' + Cast(@i AS varchar), 'U') IS NOT NULL
    SET @i+=1
Declare @TempName SysName = @TableName + '_Temp' + Cast(@i AS varchar)
Declare @Temp_Name SysName = QUOTENAME(@Schema) + '.' +QUOTENAME(@TableName + '_Temp' + Cast(@i AS varchar))

--create temp table like table code
DECLARE @object_id INT = OBJECT_ID(@Table_Name)
;WITH index_column AS 
(
    SELECT 
          ic.[object_id]
        , ic.index_id
        , ic.is_descending_key
        , ic.is_included_column
        , c.name
    FROM sys.index_columns ic WITH (NOWAIT)
    JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
    WHERE ic.[object_id] = @object_id
),
fk_columns AS 
(
     SELECT 
          k.constraint_object_id
        , cname = c.name
        , rcname = rc.name
    FROM sys.foreign_key_columns k WITH (NOWAIT)
    JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id 
    JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
    WHERE k.parent_object_id = @object_id
)
SELECT @SQLCreateTable = 'CREATE TABLE ' + @Temp_Name + CHAR(13) + '(' + CHAR(13) + STUFF((
    SELECT CHAR(9) + ', [' + c.name + '] ' + 
        CASE WHEN c.is_computed = 1
            THEN 'AS ' + cc.[definition] 
            ELSE UPPER(tp.name) + 
                CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') 
                       THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                    WHEN tp.name IN ('decimal', 'numeric')
                       THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                    ELSE ''
                END +
                CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
                CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END + 
                CASE WHEN ic.is_identity = 1 AND COL_NAME(ic.object_id, ic.column_id) <> @ColumnName THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS VARCHAR(10)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS VARCHAR(10)) + ')' ELSE '' END 
        END + CHAR(13)
    FROM sys.columns c WITH (NOWAIT)
    JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
    LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
    LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
    LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
    WHERE c.[object_id] = @object_id
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
    + ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' + 
                    (SELECT STUFF((
                         SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
                         FROM sys.index_columns ic WITH (NOWAIT)
                         JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
                         WHERE ic.is_included_column = 0
                             AND ic.[object_id] = k.parent_object_id 
                             AND ic.index_id = k.unique_index_id     
                         FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
            + ')' + CHAR(13)
            FROM sys.key_constraints k WITH (NOWAIT)
            WHERE k.parent_object_id = @object_id 
                AND k.[type] = 'PK'), '') + ')'  + CHAR(13)
    + ISNULL((SELECT (
        SELECT CHAR(13) +
             'ALTER TABLE ' + @Temp_Name + ' WITH' 
            + CASE WHEN fk.is_not_trusted = 1 
                THEN ' NOCHECK' 
                ELSE ' CHECK' 
              END + 
              ' ADD CONSTRAINT [' + fk.name  + '] FOREIGN KEY(' 
              + STUFF((
                SELECT ', [' + k.cname + ']'
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')' +
              ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
              + STUFF((
                SELECT ', [' + k.rcname + ']'
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')'
            + CASE 
                WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' 
                WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
                WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' 
                ELSE '' 
              END
            + CASE 
                WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
                WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
                WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'  
                ELSE '' 
              END 
            + CHAR(13) + 'ALTER TABLE ' + @Temp_Name + ' CHECK CONSTRAINT [' + fk.name  + ']' + CHAR(13)
        FROM sys.foreign_keys fk WITH (NOWAIT)
        JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
        WHERE fk.parent_object_id = @object_id
        FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
    + ISNULL(((SELECT
         CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END
                + CASE i.type WHEN 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED' END
                + ' INDEX [' + i.name + '] ON ' + @Temp_Name + ' (' +
                STUFF((
                SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
                FROM index_column c
                WHERE c.is_included_column = 0
                    AND c.index_id = i.index_id
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'  
                + ISNULL(CHAR(13) + 'INCLUDE (' + 
                    STUFF((
                    SELECT ', [' + c.name + ']'
                    FROM index_column c
                    WHERE c.is_included_column = 1
                        AND c.index_id = i.index_id
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '')  + CHAR(13)
        FROM sys.indexes i WITH (NOWAIT)
        WHERE i.[object_id] = @object_id
            AND i.is_primary_key = 0
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
    ), '')

--rename primary key constaint on column from table
Declare @PrimaryName SYSNAME
SELECT @PrimaryName=i.name
    from sys.index_columns ic WITH (NOWAIT)
    JOIN sys.indexes i WITH (NOWAIT)
    ON i.object_id = ic.object_id and i.index_id = ic.index_id
    where i.object_id = OBJECT_ID(@Table_Name) and Col_Name(ic.object_id, column_id) = @ColumnName and is_primary_key = 1
SET @SQL = N'EXEC sp_rename ' + @PrimaryName + ', ' + 'ThisMustNotBeExist'+ @PrimaryName + ', N''OBJECT'''
EXECUTE sp_executesql @SQL
--rename index on column from table
Declare @IndexName SYSNAME
SELECT @IndexName=i.name
    from sys.index_columns ic WITH (NOWAIT)
    JOIN sys.indexes i WITH (NOWAIT)
    ON i.object_id = ic.object_id and i.index_id = ic.index_id
    where i.object_id = OBJECT_ID(@Table_Name) and Col_Name(ic.object_id, column_id) = @ColumnName and is_primary_key = 0
SET @SQL = N'EXEC sp_rename ' + @Table_Name + '.' + @IndexName + ', ' + 'ThisMustNotBeExist'+ @IndexName + ', N''INDEX''' 
EXECUTE sp_executesql @SQL

--run create temp table like table code
EXECUTE sp_executesql @SQLCreateTable

--Move table data to temp data
SET @SQL = N'ALTER TABLE '+ @Table_Name + ' SWITCH TO ' + @Temp_Name
EXECUTE sp_executesql @SQL

--check clean up 
SET @SQL = N'IF EXISTS (SELECT * FROM '+ @Table_Name + ') THROW 50000, ''Table is not clear'', 1'
EXECUTE sp_executesql @SQL

--drop table
SET @SQL = N'DROP TABLE '+ @Table_Name
EXECUTE sp_executesql @SQL

--rename temp table to table
EXEC sys.sp_rename @Temp_Name, @TableName, 'OBJECT';

--create foreign keys
EXECUTE sp_executesql @SQLFK

Важное внимание:

Этот код копирует только ключи и индексы. Если у вас есть другие вещи в этой таблице, вы потеряете их после запуска этого кода.

если вы также хотите копировать триггеры или расширенные свойства таблицы, проверьте этот ответ и добавьте @SQL2 и @SQL3 в @SQLCreateTable перед его выполнением.

0

Источник

user10027033 24 ноя '23 в 22:24

ALTER TABLE TableName ALTER COLUMN columnName DROP IDENTITY IF EXISTS;

-1

Источник

user8900527 02 авг '23 в 07:36

Другие вопросы по тегам sql sql-server t-sql

user210496 22 ноя '11 в 16:37 2011-11-22 16:37 · Accepted Answer · 2011-11-22 16:37

Вы не можете удалить IDENTITY спецификация однажды установлена.

Чтобы удалить весь столбец:

ALTER TABLE yourTable
DROP COLUMN yourCOlumn;

Информация об ALTER TABLE здесь

Если вам нужно сохранить данные, но удалите IDENTITY колонке, вам нужно будет:

Создать новый столбец
Перенос данных из существующего IDENTITY столбец к новому столбцу
Бросить существующий IDENTITY колонка.
Переименуйте новый столбец в исходное имя столбца