-
find identity column....
Hi:
There is a Identity Crisis topic in SQLCentral. From it, draws a discussion on how to find all identity columns from a database. I want to share the following one which I had modified a bit from one sender(without using syscolumns directly):
SELECT table_name, column_name, ordinal_position orgPostion, data_type
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
where COLUMNPROPERTY (OBJECT_ID(Table_Name),Column_Name,'IsIdentity') = 1
-D
-
thanks DLU,
i thought there would be an easier way to find all tables with identities!!!!
this helps me alot, as I work alot with replication.
-
I use this
select case COLUMNPROPERTY (a.id,b.name,'IsIdentity') when 0 then 'False'
when 1 then 'True' end as IsIdentity
,a.name as TableName ,b.name as COlumnName from sysobjects a, syscolumns b
where a.type in('u','v') and
a.id = b.id
and a.name <> 'dtproperties' order by a.name
-
thanks Mak,
yes, that also works.
This is what i use without using the system function:
--Find IDENTITIES
select
'TableName' = o.name,
'ColumnName' = c.name,
*
from sysobjects o
inner join syscolumns c
on o.id = c.id
where c.status = 128
and o.type = 'u'
and o.status > 0
order by o.name
BooksOnline: SYSCOLUMNS TABLE
cloumn: status
data type: tinyint
description: Bitmap used to describe a property of the column or the parameter:
0x08 = Column allows null values.
0x10 = ANSI padding was in effect when varchar or varbinary columns were added. Trailing blanks are preserved for varchar and trailing zeros are preserved for varbinary columns.
0x40 = Parameter is an OUTPUT parameter.
0x80 = Column is an identity column.
The last sentence indicates the hex value 0x80 which is 128 in decimal value is an IDENTITY COLUMN.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|