Is there a way to read a system table to determine if a specific table has
an identity column?

Derek Hart

Use the OBJECTPROPERTY() function for this (tip: TableHasIdentity).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"Derek Hart" <derekmhart (AT) yahoo (DOT) com> wrote

SELECT OBJECTPROPERTY(OBJECT_ID('tablename'), 'TableHasIdentity');

See OBJECTPROPERTY in Books Online for more information.




"Derek Hart" <derekmhart (AT) yahoo (DOT) com> wrote

Derek Hart wrote:
Always state what version you are using.

In 2000:
....
EXISTS
(SELECT *
FROM dbo.syscolumns AS C
WHERE id = OBJECT_ID('dbo.tablename')
AND COLUMNPROPERTY(id,name,'IsIdentity')=1) ;

In 2005:
....
EXISTS
(SELECT *
FROM sys.identity_columns
WHERE object_id = OBJECT_ID('dbo.tablename')) ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

In SQL Server 2005:

SELECT 'yes'
WHERE EXISTS
(
SELECT *
FROM sys.columns
WHERE
object_id = object_id('yourtablename')
AND is_identity = 1
)


In SQL Server 2000:


SELECT 'yes'
WHERE EXISTS
(
SELECT *
FROM syscolumns
WHERE
id = object_id('yourtablename')
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
)


--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--


"Derek Hart" <derekmhart (AT) yahoo (DOT) com> wrote

Nice, now we have many ways to do it:

2000 / 2005:
OBJECTPROPERTY

2000 (and 2005 via the compatability view):
COLUMNPROPERTY over the syscolumns table

2005 only:
Is_Identity from the sys.columns view
sys.Identity_Columns view (that's one I've missed until now -- thanks for
pointing it out, David)


--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--


"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote