What system table houses Keys and Identities definitions

Question

What system table houses Keys and Identities definitions

Express12

SSCoach

Points: 18514
More actions
April 27, 2005 at 7:57 am

#362510

What system table houses Keys and Identities definitions?
Does someone have sample SQL to derive these object definitions?
thx

BT

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

sysindexes and sysindexkeys for the keys. and for IDENTITIES you might have luck with IDENT_CURRENT, IDENT_INCR and IDENT_SEED. Along with maybe OBJECTPROPERTY, COLUMNPROPERTY and INDEXKEY_PROPERTY

Maybe this will get you going:

SELECT

CAST(SO.[name] AS CHAR(20)) AS TableName

, CAST(SI.[name] AS CHAR(30)) AS IndexName

, CAST(SC.[name] AS CHAR(15)) AS ColName

, CAST(ST.[name] AS CHAR(10)) AS TypeVal

, CASE

WHEN (SI.status & 16)<>0 THEN 'Yes' ELSE 'No'

END AS ClusteredIndex

FROM

SYSOBJECTS SO

INNER JOIN

SYSINDEXES SI

INNER JOIN

SYSINDEXKEYS SIK

ON

SIK.[id] = SI.[id]

AND

SIK.indid = SI.indid

INNER JOIN

SYSCOLUMNS SC

INNER JOIN

SYSTYPES ST

ON

SC.xtype = ST.xtype

ON

SIK.[id] = SC.[id]

AND

SIK.colid = SC.colid

ON

SO.[id] = SI.[id]

WHERE

SO.xtype = 'u'

AND

SI.indid > 0

AND

SI.indid < 255

AND

(SI.status & 64)=0

ORDER BY

TableName

, IndexName

, SIK.keyno

SELECT

CAST(OBJECT_NAME(sysidxkeys.id) AS CHAR(20)) AS Tabelle

, CAST(sysidx.name AS CHAR(20)) AS [Index]

, CAST(syscol.name AS CHAR(20)) AS Spalte

, sysidxkeys.keyno AS Index_Nr

, CASE INDEXKEY_PROPERTY(sysidxkeys.id, sysidxkeys.indid, sysidxkeys.keyno, 'IsDescending')

WHEN 1

THEN 'DESC'

ELSE 'ASC'

END AS Sortierreihenfolge

FROM

sysindexkeys sysidxkeys

INNER JOIN

sysindexes sysidx

ON

sysidxkeys.id = sysidx.id

AND

sysidxkeys.indid = sysidx.indid

INNER JOIN

syscolumns syscol

ON

sysidxkeys.id = syscol.id

AND INDEXKEY_PROPERTY(sysidxkeys.id, sysidxkeys.indid, sysidxkeys.keyno, 'ColumnID') = syscol.colid

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

Do you mean 'keys' as in 'declared primary keys'..?

If so, perhaps it's better to not bother about which tables are involved, just let sp_helpconstraint suffice. (look inside sp_helpconstraint and you'll know what I mean)

/Kenneth

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Frank, How are you?

I think the Identities definitions is kept in syscolumns table in user database with status = 0x80.

sp_help yourtable name will show all information you need.

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

Maybe I've overshoot the mark here.

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

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

Allen, I'm fine. And you?

Yes, you're right. 0x80 identifies an IDENTITY column.

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

Express12 SSCoach Points: 18514 More actions · Answer 6

Thanks for the infor so far -- I have 2 queries attached here. My goal: Using a source SQL 2K DB as input, generate a SQL script containing DDL for a a target DB creation.

The 1st statement avails 99% of my source table schema info. The 2nd statement identifies tables w/ Identity columns. It does 99% for what I need on step #1 (CREATE table). What would you suggest I add to have a "As IsIdentity" column? That would be the most ideal.

SELECT

objs.[ID] AS TableObjectID,

objs.[name] AS TableName,

cols.[name] AS FieldName,

cols.[length] AS FieldLength,

cols.[prec] AS Prec,

cols.[scale] AS Scale,

cols.[isnullable] As FieldNullable,

cols.[collation] As FieldCollation,

types.[name] AS TypeName

FROM syscolumns as cols

INNER JOIN systypes types ON types.xtype=cols.xtype

INNER JOIN sysobjects objs ON objs.id=cols.ID

WHERE (objs.xtype='U' OR objs.xtype='V') AND types.[name]<>'sysname'

ORDER BY objs.[ID], cols.[colorder]

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

order by 2

BT

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

You are aware that the next version might turn the system tables upside down? I would see if you can't utilize the INFORMATION_SCHEMA views for most of this stuff.

I'm not sure about what you're after. Are you trying to reinvent the wheel? Can't you simply script the db and run this via OSQL?

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

Express12 SSCoach Points: 18514 More actions · Answer 8

Frank - my sentiments exactly! (regarding the reinventing of the wheel) -- We have a "chief" architect insisting that we generate an .MSI install script -- to do so, he needs to dynamically render the DB objects (rather than using SQL-DMO)

He's coding C# to accomodate this and has solicited this info from yours truly (the friendly DBA!)

BT

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

Ouch! Sorry to hear that.

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

iadams SSCommitted Points: 1970 More actions · Answer 10

I think you should let MSSQL2K generate the create database scripts for you. If you try to do it yourself you might miss something important. Its possible to do what the internal tools do, but its to risky. And the system tables shouldent be relied upon, they can and will change, the Information schema is designed to take care of this change.

-Isaiah