[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Retreiving Identity attributes

Posted on 2001-09-05
4
Medium Priority
?
256 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 800 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

649 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