Solved

Retreiving Identity attributes

Posted on 2001-09-05
4
253 Views
Last Modified: 2013-12-25
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?
0
Comment
Question by:Éric Moreau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 39

Expert Comment

by:appari
ID: 6459373

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
 
LVL 70

Author Comment

by:Éric Moreau
ID: 6460412
This is working great but ... how can I know which field has the identity property set to Yes?
0
 
LVL 39

Accepted Solution

by:
appari earned 200 total points
ID: 6460780
SELECT name
from syscolumns
where id =OBJECT_ID('employees')
and COLUMNPROPERTY( OBJECT_ID('employees'),name,'IsIdentity')=1

0
 
LVL 70

Author Comment

by:Éric Moreau
ID: 6460859
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question