list of tables with identity field.

Question

list of tables with identity field.

Linata

Ten Centuries

Points: 1370
More actions
April 22, 2004 at 2:39 am

#161810

I need to get a list of tables where there is an identity field. I didn't find these information in syscolumns.
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jbonapart SSC Rookie Points: 36 More actions · Answer 1

The AUTOVAL column of the syscolumns table is NOT NULL if the column has the identity on. The following query illustrate an exampe. In combination with information in the view INFORMATION_SCHEMA.COLUMNS you can do a lot. Hope this help.

SELECT syscolumns.NAME, syscolumns.AUTOVAL

FROM syscolumns, sysobjects

WHERE syscolumns.ID = sysobjects.ID

and sysobjects.name = 'TABLE_NAME'

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 2

if your identity cols are all primary keys you could use

select CONSTRAINT_CATALOG AS [Database], TABLE_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

where CONSTRAINT_TYPE = 'PRIMARY KEY'

which will return the database name and table name

not ideal but it might get you a bit closer

cheers

dbgeezer

jbonapart SSC Rookie Points: 36 More actions · Answer 3

This will do it for a specific database. cheers .

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

Linata Ten Centuries Points: 1370 More actions · Answer 4

Linata

Ten Centuries

Points: 1370

April 22, 2004 at 4:55 am

#503780

Thanks evebody.

You realy help me.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

For the sake of completeness, you can also take advantage of the mostly overlooked OBJECTPROPERTY stuff like this

SELECT

*

FROM

sysobjects

WHERE

OBJECTPROPERTY(id,'TableHasIdentity')=1

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

rockmoose SSCarpal Tunnel Points: 4571 More actions · Answer 6

Some MetaData from INFORMATION_SCHEMA.COLUMNS in case You don't want to select from systables..

/rockmoose

SELECT

ic.ORDINAL_POSITION,

ic.COLUMN_NAME,

ic.DATA_TYPE,

ic.CHARACTER_MAXIMUM_LENGTH,

ic.NUMERIC_PRECISION,

ic.NUMERIC_SCALE,

IS_IDENTITY = COLUMNPROPERTY ( OBJECT_ID( ic.TABLE_CATALOG + '.' + ic.TABLE_SCHEMA + '.' + ic.TABLE_NAME ) , ic.COLUMN_NAME , 'IsIdentity' ),

IS_NULLABLE = CASE UPPER(ic.IS_NULLABLE) WHEN 'YES' THEN 1 ELSE 0 END,

IS_COMPUTED = COLUMNPROPERTY ( OBJECT_ID( ic.TABLE_CATALOG + '.' + ic.TABLE_SCHEMA + '.' + ic.TABLE_NAME ) , ic.COLUMN_NAME , 'IsComputed' ),

COLUMN_DEFAULT = ic.COLUMN_DEFAULT

FROM

INFORMATION_SCHEMA.COLUMNS ic

WHERE

ic.TABLE_SCHEMA = 'dbo'

AND ic.TABLE_NAME = 'MY_Table_Neme'

ORDER BY

ic.ORDINAL_POSITION

You must unlearn what You have learnt