Using Information_Schema - Find auto-increment primary key

[firstdivision]

Hi,

I'm using the INFORMATION_SCHEMA tables to get the information to generate Sql statements. One unique problem I've come across is how to identify if a column is an auto-increment field. I need to know this so that I can write the insert statement without this field.

Here's the two queries I'm using so far, but I don't see anything about an auto-increment status...

SELECT * FROM Information_SCHEMA.columns


SELECT * FROM 
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS, 
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE 
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME 
	AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
	AND CONSTRAINT_TYPE = 'PRIMARY KEY'

Any ideas for other information_schema places to look??

Thanks,
FD

 

[SQLDenis]

SELECT COLUMN_NAME,CASE when COLUMNPROPERTY( OBJECT_ID(TABLE_NAME),COLUMN_NAME,'ISIdentity') =1 then 'IDENTITY'
else 'NOT IDENTITY' end IsIdentity
FROM INFORMATION_SCHEMA.COLUMNS

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[firstdivision]

Sweet! That worked perfect.

Thanks Denis,
FD