September 21, 2004 at 10:38 am
Can anyone tell me how to determine whether a column is an identity column when querying the system tables. I know how to list the columns in the tables and get the column type, length, precision, etc. I cannot figure out how to tell if a column is an identity column.
TIA for any help
September 21, 2004 at 11:05 am
I believe the autoval column in syscolumns has a value for identity coluymns.
September 22, 2004 at 2:51 am
Extract from BOL
| status | tinyint | Bitmap used to describe a property of the column or the parameter: 0x08 = Column allows null values. 0x10 = ANSI padding was in effect when varchar or varbinary columns were added. Trailing blanks are preserved for varchar and trailing zeros are preserved for varbinary columns. 0x40 = Parameter is an OUTPUT parameter. 0x80 = Column is an identity column. |
Regards,Iain
September 22, 2004 at 6:40 am
THank you - I sure missed that.
September 22, 2004 at 7:01 am
There is an easier way. Try looking at the ObjectProperties and ColumnProperties functions:
Simple example from BOL:
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname','IsIdentity')
A query on the Information Schema Views for all identity columns:
SELECT
OBJECT_ID(col.Table_Name) AS [Object_ID]
, COLUMNPROPERTY(
OBJECT_ID(col.Table_Name)
, col.Column_Name,'IsIdentity'
) AS [IsIdentity]
, *
FROM
Information_Schema.Columns col
WHERE
COLUMNPROPERTY(
OBJECT_ID(col.Table_Name)
, col.Column_Name,'IsIdentity') = 1
Good luck,
Joshua
September 27, 2004 at 11:23 am
The proc below lists all tables in a database that contain identity columns (auto-incremet values). It skips System tables but you can change that setting. Modify the database setting, etc. for your site
Use ve_common -- Database to use
/*
List Tables with identity columns (auto-increment)
*/
Select o.Name [Table], c.Name [Column], c.xtype, c.number, c.type, c.autoval
from sysobjects o, syscolumns c
Where o.id = c.id
and o.xtype <> 'S' -- Skip system tables
-- and o.name = 'EMSCase' -- Check one table only
and c.autoval Is not NULL
Order by 1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply