May 16, 2005 at 5:37 am
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
May 16, 2005 at 6:11 am
Hi
u can use this statement to know about identity column in a DB .
Select * from syscolumns where status= 0x80
Padmakumar
May 16, 2005 at 6:31 am
where Status = 0x80 and id = object_id('TableName')
May 20, 2005 at 6:57 pm
SELECT sysobjects.name as 'Table Name', syscolumns.NAME AS 'Identiy Column Name'
FROM syscolumns, sysobjects
WHERE syscolumns.ID = sysobjects.ID
and sysobjects.name in (select name
FROM sysobjects where xtype = 'U' )
and syscolumns.AUTOVAL IS NOT NULL
May 22, 2005 at 2:38 am
To add to the above, since accessing the system tables directly is not recommended, here is another way to check if a table has an identity column:
SELECT OBJECTPROPERTY(OBJECT_ID('owner.tablename'), 'TableHasIdentity')
To check if a column has an identity property:
SELECT COLUMNPROPERTY(OBJECT_ID('owner.tablename'), 'column_name', 'IsIdentity')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply