SQL syntax to alter identity property of a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL syntax to alter identity property of a column

Author

Topic

albear
Starting Member

2 Posts

Posted - 2006-10-30 : 12:07:39

i have a rather intresting problem i need to change all the identity colums from Yes, to Yes, NOT FOR REPLICATION, in quite a few databases

now the only way i can find of doing this is the say SQL server does it its self

see code below

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
ALTER TABLE dbo.AllowedTemplates
DROP CONSTRAINT FK_AllowedTemplates_Content
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.AllowedTemplates
DROP CONSTRAINT DF_AllowedTemplates_AT_DisplayOrder
GO
CREATE TABLE dbo.Tmp_AllowedTemplates
(
ATID int NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION,
PTID int NOT NULL,
CID int NOT NULL,
AT_DisplayOrder int NOT NULL,
ParentPTID int NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_AllowedTemplates ADD CONSTRAINT
DF_AllowedTemplates_AT_DisplayOrder DEFAULT (0) FOR AT_DisplayOrder
GO
SET IDENTITY_INSERT dbo.Tmp_AllowedTemplates ON
GO
IF EXISTS(SELECT * FROM dbo.AllowedTemplates)
EXEC('INSERT INTO dbo.Tmp_AllowedTemplates (ATID, PTID, CID, AT_DisplayOrder, ParentPTID)
SELECT ATID, PTID, CID, AT_DisplayOrder, ParentPTID FROM dbo.AllowedTemplates TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_AllowedTemplates OFF
GO
DROP TABLE dbo.AllowedTemplates
GO
EXECUTE sp_rename N'dbo.Tmp_AllowedTemplates', N'AllowedTemplates', 'OBJECT'
GO
ALTER TABLE dbo.AllowedTemplates ADD CONSTRAINT
PK_AllowedTemplates PRIMARY KEY CLUSTERED
(
ATID
) WITH FILLFACTOR = 90 ON [PRIMARY]

GO
ALTER TABLE dbo.AllowedTemplates WITH NOCHECK ADD CONSTRAINT
FK_AllowedTemplates_Content FOREIGN KEY
(
CID
) REFERENCES dbo.Content
(
CID
)
GO
COMMIT

now i either need another way to do this (which i think is impossable) or i need a way to script all the tables within a database into this format, just wondering if anyone had come across this before and was able to help in any way.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-30 : 12:20:21

how about
alter table AllowedTemplates drop column ATID
alter table AllowedTemplates add ATID int NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

albear
Starting Member

2 Posts

Posted - 2006-10-30 : 12:30:14

Yep i can see how that would work for a new database.

But all the current databases are filled with data and the identity colum is used as the primary key, so if i drop it none of the data will match up, as the identity will be reset.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources