Post reply

INFORMATION_SCHEMA and identity columns

  • June 24, 2004 at 8:24 am

    #152200

    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?

     

     

     

     

  • June 24, 2004 at 12:28 pm

    #511845

    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.

  • June 24, 2004 at 1:34 pm

    #511866

    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

  • June 24, 2004 at 3:09 pm

    #511879

    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]

  • June 25, 2004 at 2:29 am

    #511915

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

    Cheers

  • June 25, 2004 at 2:48 am

    #511917

    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