Post reply

How to get the identity column name of a table

  • 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

  • September 7, 2006 at 3:42 am

    #659005 

    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

  • September 7, 2006 at 5:00 am

    #659012

    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

    --Ramesh

  • September 8, 2006 at 3:35 am

    #659297

    "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.

  • September 8, 2006 at 2:00 pm

    #659452

    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

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

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