Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2003-03-24
7
Medium Priority
?
386 Views
Last Modified: 2008-02-01
Hello,

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.
0
Comment
Question by:sac666
7 Comments
 
LVL 23

Accepted Solution

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

Expert Comment

by:russellshome
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.
0
 
LVL 13

Expert Comment

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

select IDENTITYCOL from YourTable
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 43

Expert Comment

by:Eugene Z
ID: 8202738
--One more

USE pubs
GO

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

Expert Comment

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

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

Expert Comment

by:ispaleny
ID: 8203427
You prefer standard information_schema tables, try this

select *
,"IsIdentity"=COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)),COLUMN_NAME,N'IsIdentity')
from information_schema.columns

Or simpler with a function

--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
 set @bit=COLUMNPROPERTY(OBJECT_ID(QUOTENAME(@TABLE_SCHEMA)+'.'+QUOTENAME(@TABLE_NAME)),@COLUMN_NAME,N'IsIdentity')
 return @bit
end
GO

--query
select *
,"IsIdentity"=dbo.ufn_IsIdentity(TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
from information_schema.columns








0
 
LVL 1

Author Comment

by:sac666
ID: 8216353
Hello,

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

Sachin
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

580 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