Determine If Table Has Identity Field

Is there a way to read a system table to determine if a specific table has
an identity column?
Derek HartSELECT OBJECTPROPERTY(OBJECT_ID('tablename'), 'TableHasIdentity');
See OBJECTPROPERTY in Books Online for more information.
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:%23fatw1GcGHA.3380@.TK2MSFTNGP04.phx.gbl...
> 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@.yahoo.com> wrote in message news:%23fatw1GcGHA.3380@.TK2MSFTNGP04.p
hx.gbl...
> Is there a way to read a system table to determine if a specific table has
an identity column?
> Derek Hart
>|||Derek Hart wrote:
> Is there a way to read a system table to determine if a specific table has
> an identity column?
> Derek Hart
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/ms130214(en-US,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@.yahoo.com> wrote in message
news:%23fatw1GcGHA.3380@.TK2MSFTNGP04.phx.gbl...
> Is there a way to read a system table to determine if a specific table has
> an identity column?
> Derek Hart
>|||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@.acm.org> wrote in message
news:1146852429.607871.92140@.v46g2000cwv.googlegroups.com...
> 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/ms130214(en-US,SQL.90).aspx
> --
>