INFORMATION_SCHEMA and identity columns

  • Quick question.  I'm trying to identify identity columns using the INFORMATION_SCHEMA, but not sure if it's possible.

    I know I can use "syscolumns.autoval is not null" to identify them, but I assume I should be using INFORMATION_SCHEMA where possible.

    Any ideas?

     

     

     

     

  • Unfortunately INFORMATION_SCHEMA views do not have that in their output. I do know there is at least one sp that will dor the trick (sp_help 'tablename') but I don't recall others.

  • you could use the COLUMNPROPERTY function to check if a column is an identity column or not...

    SELECT

    COLUMN_NAME,

    DATA_TYPE,

    CASE COLUMNPROPERTY (OBJECT_ID('<TableName>'),COLUMN_NAME ,'IsIdentity') WHEN 1 THEN 'Identity Column' ELSE '' END Identity_Column

    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<TableName>'

    HTH

  • Sometimes something like this can be useful:

    
    CREATE TABLE ID_T
    (
    id INT IDENTITY
    )
    INSERT INTO ID_T DEFAULT VALUES
    INSERT INTO ID_T DEFAULT VALUES
    SELECT IDENTITYCOL FROM ID_T
    DROP TABLE ID_T
    
    (1 row(s) affected)
    
    
    (1 row(s) affected)
    
    id          
    ----------- 
    1
    2
    
    (2 row(s) affected)
    

    If the table doesn't contain such a column, you'll get an error.

    Just my $0.02 cents.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Thanks folks, I like the look of COLUMNPROPERTY.  I'll give that a go..

    Cheers

  • Just for completeness, sp_help returns also informations when a table contains such a column.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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