Éric Moreau
asked on
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?
Any ideas?
ASKER
This is working great but ... how can I know which field has the identity property set to Yes?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_N ame) 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
I joined all that togheter. Is is the final result:
SELECT SO.Name AS TableName, SC.Name AS FieldName,
Ident_Seed(Info.Table_Name
Ident_Incr(Info.Table_Name
Ident_Current(Info.Table_N
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('
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.