June 24, 2004 at 8:24 am
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
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
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
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
Thanks folks, I like the look of COLUMNPROPERTY. I'll give that a go..
Cheers
June 25, 2004 at 2:48 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy