I got stuck at a point on removing the identity column from
a table in a database, thinking much about T-SQL scripts to remove the identity
property. However, there is no direct DDL scripts like ALTER or MODIFY tables to
remove the identity property.
Steps to remove the Identity property through GUI in SSMS:
1. Before dropping the identity, please check the
table from the database that you are referring to.
2. From the “Object Explorer”, expand the database
referring to and right-click on the table where the identity property needs to
be changed.
3. Select the “Design” option from the list, which will
open the design of the table as shown below.
4. Under the Result section, the design is popped with
the details of the table with respect to structure.
5. Now, expand the “Identity Specification” and opt
as “Yes/No” in the “Is Identity” option.
6. Click on the “Save” option to effect the changes
made.
When you click on the "Design option", the below layout pops up:
Take a look at the result section to opt the "Is Identity" changes:
Additionally, there is an option to generate a T-SQL script by the action performed using the GUI in SSMS. Since there is no ready-made script for changing the identity property, a feature in SSMS could be used to generate the T-SQL script.
/* 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
/*creating a DumpTable*/
CREATE TABLE dbo.Tmp_DumpTable
(
[COUNT] int not null identity(1,1),
fruits nvarchar not null,
vegetables nvarchar
not null
) ON UserData
TEXTIMAGE_ON UserData
GO
ALTER TABLE dbo.Tmp_DumpTable SET (LOCK_ESCALATION = TABLE)
GO
/ /*Setting the Identity_Insert to ON*/
SET IDENTITY_INSERT dbo.Tmp_DumpTable ON
GO
IF EXISTS(SELECT * FROM dbo.DumpTable)
EXEC('INSERT INTO dbo.Tmp_DumpTable (Count,Fruits,Vegetables)
SELECT Count,Fruits,Vegetables
FROM dbo.DumpTable WITH (HOLDLOCK TABLOCKX)')
GO
/ /*Setting the Identity_Insert to OFF*/
SET IDENTITY_INSERT dbo.Tmp_DumpTable OFF
GO
/*Dropping the created DumpTable*/
DROP TABLE dbo.DumpTable
GO
EXECUTE sp_rename N'dbo.Tmp_DumpTable', N'DumpTable', 'OBJECT'
GO
COMMIT
Related Post: "Reset Identity Column Values In SQL Server"
0 comments:
Post a Comment