Identity column exists for a table - How to know

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Identity column exists for a table - How to know

Author

Topic

ourspt
Starting Member

33 Posts

Posted - 2005-05-16 : 07:16:22

Hi,

Is there any way, that I can determine whether a table has any identity column, programmatically in SQL Server. In other words, is it stored some where like syscolumns or sysconstraints or whatever, whether a table has identity column and what column has the identity property set to on? I am referring to SQL Server 2000.

Thanks in advance
ourspt

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-16 : 08:02:12

[code]
SELECT
name AS TableName,
CASE
WHEN OBJECTPROPERTY(id,'TableHasIdentity') = 1
THEN 'TRUE'
ELSE 'FALSE'
END AS TableHasIdentity
FROM sysobjects
WHERE xtype = 'U'
[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-16 : 08:06:48

[code]
SELECT
so.name AS TableName,
sc.name AS ColumnName
FROM
sysobjects so
INNER JOIN syscolumns sc ON so.id = sc.id
WHERE
so.xtype = 'U'
AND COLUMNPROPERTY(so.id, sc.name,'IsIdentity') = 1
[/code]

The first one will tell you if a table has an identity column. This one lists the table name and column name. That what you were wanting?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-16 : 09:46:10

Using Sysobjects + Syscolumns = naughty
You should be using the views provided by the very nice SQL development team at the Borg...errr.. Microsoft

*need more coffee*

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources