How to identify which column has the identity and get its details

Posted on 2003-03-24
Medium Priority
Last Modified: 2008-02-01

Given the tablename i want to retrieve the NAME OF THE COLUMN  having identity and then its details if possible.
I have found out so far 2 ways of doing it but i would like to go with a more effective way if possible

So far i have tried the following

a) sp_help tablename
This gives me the name of the identity column as well as its details which is exactly what i need but along with this it also returns number of other resultsets which are not needed.

b) By querying syscolumn and checking autoval column
This way is not documented. Documentation says that autoval column is for "internal use" but the value is usually set for identity columns, for other columns it is null.
Question by:sac666
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
LVL 23

Accepted Solution

adathelad earned 500 total points
ID: 8201394
SELECT name FROM syscolumns
WHERE id = OBJECT_ID('YourTableName')
AND COLUMNPROPERTY(OBJECT_ID('YourTableName'), name, 'isIdentity') = 1

Expert Comment

ID: 8201472
If you want to ensure your application will work in future versions of SQL server then querying syscolumns is not appropriate since this table may change. You should always use information schema views or system stored procedures.

Unfortunately, information schema views do not reveal the identity column.

sp_columns is better than sp_help in this instance. in the result set, the column TYPE_NAME will include 'identity' when the column is an identity column.
LVL 13

Expert Comment

ID: 8201507
I don't know why you must find an identity column.
Try this

select IDENTITYCOL from YourTable
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

LVL 43

Expert Comment

by:Eugene Z
ID: 8202738
--One more

USE pubs

exec sp_msforeachtable @command1="print '?' DBCC CHECKIDENT ('?', NORESEED)"
LVL 43

Expert Comment

by:Eugene Z
ID: 8202771
--This is good
USE pubs

exec sp_msforeachtable @command1="print '?' exec sp_mshelpcolumns '?'"
LVL 13

Expert Comment

ID: 8203427
You prefer standard information_schema tables, try this

select *
from information_schema.columns

Or simpler with a function

create function dbo.ufn_IsIdentity(@TABLE_SCHEMA nvarchar(128),@TABLE_NAME nvarchar(128),@COLUMN_NAME nvarchar(128))
returns bit with schemabinding as begin
 declare @bit bit
 return @bit

select *
from information_schema.columns


Author Comment

ID: 8216353

This (the first answer by adathelad) has been the most direct answer to what I had requested,however I am thankful to all of you for providing different solutions which may prove useful under different scenarios.

Thankyou to all of you


Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

764 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