Solved

Retreiving Identity attributes

Posted on 2001-09-05
4
252 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

749 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