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