Retreiving Identity attributes

I need to retreive a field's Identity attributes (Identity, Seed and Increment). All this for SQL 7 / 2000 databases and using ADO, ADOX or pure SQL.

Any ideas?
LVL 71
Éric MoreauSenior .Net ConsultantAsked:
Who is Participating?
 
appariConnect With a Mentor Commented:
SELECT name
from syscolumns
where id =OBJECT_ID('employees')
and COLUMNPROPERTY( OBJECT_ID('employees'),name,'IsIdentity')=1

0
 
appariCommented:

SELECT TABLE_NAME, IDENT_INCR(TABLE_NAME) AS IDENT_INCR, IDENT_SEED(TABLE_NAME) as IDENT_SEED
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME ='employees'

replace with your table name.


0
 
Éric MoreauSenior .Net ConsultantAuthor Commented:
This is working great but ... how can I know which field has the identity property set to Yes?
0
 
Éric MoreauSenior .Net ConsultantAuthor Commented:
Great!

I joined all that togheter. Is is the final result:

SELECT SO.Name AS TableName, SC.Name AS FieldName,
     Ident_Seed(Info.Table_Name) AS IdSeed,
     Ident_Incr(Info.Table_Name) AS IdIncrement,
     Ident_Current(Info.Table_Name) AS IdCurrent
FROM SysObjects AS SO
INNER JOIN SysColumns AS SC
ON SO.Id = SC.Id
INNER JOIN information_schema.Tables AS Info
ON Info.Table_Name = SO.Name
WHERE SO.Name = 'Analyst'
AND ColumnProperty(OBJECT_ID('Analyst'), SC.Name, 'IsIdentity') = 1
0
All Courses

From novice to tech pro — start learning today.