Get column details (specifically identity fields) from SQL server schema

Hi there,

I am developing a script to automatically discover the schema information for a SQL server table, then generate an insert command on the fly.

I have one problem though - the script will work fine if the key (assuming there is one numeric key field) is an auto-incrementing identity, but will not work if it is a normal int and requires me to specify the value.

I can get around this easily enough, but only if I KNOW what type of field it is first.

So far I am getting the following information about the table:

SELECT             column_name,
                data_type,
                ISNULL(character_maximum_length,0) AS max_length,
                 is_nullable
FROM            information_schema.columns
WHERE            table_name = 'mytablename'
ORDER BY      ordinal_position

I have been searching through the schema for ages and cannot find anything that tells me whether a field is an identity seed or not.

Can anyone help?
DotSPFAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harshad09Commented:
use sp_columns tableName
The column TYPE_NAME will tell you whether the column is identity.
For details refer sp_columns sp code in SQL Server

Thanks,
Harshad
amit_gCommented:
select Status, * from syscolumns where id = (select id from sysobjects where name = 'Authors')

Status value of 128 means that the column is identity.


arbertCommented:
Avoid the system tables unless you have too.  Use the built in property functions:

SELECT COLUMNPROPERTY( OBJECT_ID('yourtablename'),'yourcolumnName','IsIdentity')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DotSPFAuthor Commented:
cheers guys, liked the last one best I'm afraid!

for reference, here is my final query

SELECT             *,(SELECT COLUMNPROPERTY(OBJECT_ID('mytable'),column_name,'IsIdentity')) AS is_identity
FROM            (
                SELECT             column_name,
                                     data_type,
                                     ISNULL(character_maximum_length,0) AS max_length,
                                     is_nullable,
                                     ordinal_position
                FROM            information_schema.columns
                WHERE             table_name = 'mytable'
                ) DERIVED
ORDER BY ordinal_position
arbertCommented:
Looks good mate!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.