Identity column problem

[tapks]

I have a table of 3 columns in MS SQL Server, out of which one column is of integer datatype. I want to modify this column to Identity Column. It is possible to do it through Enterprise Manager. But how to do it through MS SQL Query Analyser ?

Pl help me out.

 

[tlbroadbent]

You can't Alter the column directly. I believe that when Enterprise Manager makes the change it actually drops the column and then adds it as an identity column. You can do the same in code.

Alter Table dbo.tbl_name Drop column recid
go
Alter Table dbo.tbl_name Add recid int identity(1,1)
go Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[chiph]

Terry -

You're right -- BOL says you can't do a ALTER TABLE ALTER COLUMN and set the IDENTITY attribute -- you can only do it on an initial add. Bummer!

Chip H.

 

[tapks]

First of all thanks for the reply.

This above solution is ok if the table is empty. But in this way I will loose the data of that column. So how to protect the same?

 

[RickCole]

Use the SET IDENTITY_INSERT option. Sample script below.

Rick.


CREATE TABLE dbo.Tmp_THREAD_SEQUENCE
(THRS_SEQUENCE_NUM int NOT NULL IDENTITY (1, 1))

SET IDENTITY_INSERT dbo.Tmp_THREAD_SEQUENCE ON

IF EXISTS(SELECT * FROM dbo.THREAD_SEQUENCE)
EXEC('INSERT INTO dbo.Tmp_THREAD_SEQUENCE(THRS_SEQUENCE_NUM)
SELECT THRS_SEQUENCE_NUM')

SET IDENTITY_INSERT dbo.Tmp_THREAD_SEQUENCE OFF

DROP TABLE dbo.THREAD_SEQUENCE

EXECUTE sp_rename 'dbo.Tmp_THREAD_SEQUENCE', 'THREAD_SEQUENCE'

 

[tapks]

Thankx...

 

[Guest_imported]

How to find out a particular column name in present in all the tables in a database and return the name of those tables

 

[tlbroadbent]

Run this query in any database to see all info about a column name including all tables where it is used.

Select * From Information_schema.columns
Where column_name='mycolumn' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.