How to get the identity column name of a table

Question

How to get the identity column name of a table

Ramesh Saive

SSC-Insane

Points: 24275
More actions
September 7, 2006 at 3:10 am

#116676

Hi all,
I just want to get the identity column of a table to a variable... I know i can do it using a UDF/SP..
Is there is any simple way tod do?

--Ramesh
--Ramesh

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76001 More actions · Answer 1

DECLARE @TableName SysName ,
@ColumnName SysName
SET @TableName = 'tblAccommodation'

SELECT @ColumnName=COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name=@TableName
AND COLUMNPROPERTY(Object_Id(TABLE_NAME),COLUMN_NAME,'IsIdentity')=1


PRINT @ColumnName

LinkedIn Profile

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

David,

I'd used this one.. it is ok?

SELECT @strIdentity = name FROM dbo.syscolumns WHERE id = OBJECT_ID( @strTableName ) AND colstat & 1 = 1

--Ramesh

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 3

"Information schema" and the listed functions are better to use as the underlying "system catalog" tables are liable to change/be made obselete by MS, whereas the "schema" views are less likely to change.

David.Poole SSC Guru Points: 76001 More actions · Answer 4

I think of the INFORMATION_SCHEMA views as being something like a programming interface. I know interfaces don't contain implementation, but as Andrew says, the underying tables may change but the views themselves provide a window onto where the data is actually held.

LinkedIn Profile