January 21, 2003 at 10:26 am
How can I identify the IDENTITY column (if one created) on a table.
January 21, 2003 at 11:55 am
SELECT OBJECTPROPERTY(OBJECT_ID('YourTableName'),'TableHasIdentity') to check whether the table has identity column.
SELECT COLUMNPROPERTY( OBJECT_ID('YourTableName'),'YourColName','IsIdentity') to check column 'YourColName' is a identity column.
January 21, 2003 at 11:59 am
Is there a column in syscolumns or information_schema.columns that can tell us what columns are IDENTITY columns?
January 21, 2003 at 12:15 pm
Check the status of syscolumns. if status is 0x80, The column is an identity column.
January 21, 2003 at 12:36 pm
Why does the status column for an IDENTITY column have a value of 128?
January 22, 2003 at 8:07 am
Decimal number 128 is equal to Hexdecimal Numner x080.
January 22, 2003 at 8:49 am
IMHO I believe the status column contains a bit masked value containing the
values that define the column. I believe the COLUMNPROPERTY ( id , column ,
property ) function returns whether or not one the bits have been set in that
mask.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 23, 2003 at 11:48 pm
This is another approach to do.....
IF EXISTS(SELECT * FROM ABCDE..SYSCOLUMNS WHERE ID = OBJECT_ID('ABCDE.DBO.DIM_ALLOCATED_COST') AND COLSTAT = 1)
SET IDENTITY_INSERT ABCDE.DBO.DIM_ALLOCATED_COST ON
GO
INSERT INTO ABCDE.DBO.DIM_ALLOCATED_COST (
D_D_ALLOC_MIS_DATE,FIC_MIS_DATE,N_D_ALLOC_TXN_COST,V_D_ALLOC_TXN_BRN_CODE,V_D_ALLOC_TXN_CHAN_CODE,V_D_ALLOC_TXN_CODE)
SELECT
D_D_ALLOC_MIS_DATE,FIC_MIS_DATE,N_D_ALLOC_TXN_COST,V_D_ALLOC_TXN_BRN_CODE,V_D_ALLOC_TXN_CHAN_CODE,V_D_ALLOC_TXN_CODE FROM UATDAY1DBO.DIM_ALLOCATED_COST
PRINT 'ABCDE.DBO.DIM_ALLOCATED_COST: RECORDS INSERTED :' + CONVERT(VARCHAR(20),@@ROWCOUNT )
GO
IF EXISTS(SELECT * FROM ABCDE..SYSCOLUMNS WHERE ID = OBJECT_ID('ABCDE.DBO.DIM_ALLOCATED_COST') AND COLSTAT = 1)
SET IDENTITY_INSERT ABCDE.DBO.DIM_ALLOCATED_COST OFF
Cheers
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
January 27, 2003 at 8:59 am
Try this:
select syscolumns.name from syscolumns inner join sysobjects on syscolumns.id = sysobjects.id where sysobjects.name = 'tablename_here' and syscolumns.status & 0x80 <> 0
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply