Get Identity Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Get Identity Column

Author

Topic

tsturm
Starting Member

7 Posts

Posted - 2004-10-21 : 10:57:03

Hi ev´ryone,
is there any (more or less) server independent way of finding the identity-column of a given table?
I am trying to build a web based table control that should work with any (well, almost...) SQL database server; thus, I cannot go the "select from systables..." way. Currently, I am using a query of the information schema 12 (adSchemaIndexes) to retrieve the name of the id-column: However, this only works if the identity-column is also a primary key. While this is generally a good idea, it´s unfortunately not always the case. So how can I determine that

CREATE TABLE [dbo].[SERVER] (
[SRVID] [int] IDENTITY (1, 1) NOT NULL ,
[SERVER] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
...

in my table "Server" the identity-column is "SRVID" when it´s NOT a primary-key?
any help would be really appreciated!

thanx for reading,
tom

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 11:30:17

How do the Reverse Engineering tools (database diagramming etc.) generate the CREATE TABLE syntax? That might give you a clue

Kristen

tsturm
Starting Member

7 Posts

Posted - 2004-10-21 : 11:59:42

...and the solution is:
select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 and TABLE_NAME = 'SERVER'

though wether that´ll work with MySQL for instance?!
can anyone give it a spin?

cheers -
Tom

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-21 : 12:02:48

COLUMNPROPERTY() maybe...
> "work with any (well, almost...) SQL database server"
Only MS Sql Server or Other Vendors as well ?

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-21 : 12:04:19

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 12:04:23

I had had a look in INFORMATION_SCHEMA.COLUMNS to see if it had an Identity column - it didn't :-( So anything else is database-dependant I suspect ...

How about if you made a rule that all auto-increment columns have to be called "ID"? <g>

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-21 : 12:06:29

You should have different Metadata scripts for each database you are trying to support.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

tsturm
Starting Member

7 Posts

Posted - 2004-10-22 : 07:43:36

@Kristen: run the INFORMATION_SCHEMA query without the "and TABLE_NAME='SERVER' in Query Analyzer. Should return all identity columns of the database you´re connected to with the respective tables...
@rockmoose: <qoute>"The INFORMATION_SCHEMA is part of the SQL-92 standard,"</quote>blah (from http://www.devx.com/getHelpOn/10MinuteSolution/20561)...but I think you´re right; since not all schema-querys are supported by MS SQL 2000 for instance, I´d have to check wether the query you want to run is supported anyway. Currently, if you call the function objcon.openschema(2,array(strDB,"dbo",null)) you get:

ADODB.Connection error '800a0cb3'
Object or provider is not capable of performing requested operation.

which - since SQL 2000 doesn´t support adSchemaCatalogs is a perfectly correct error ;-))

Kristen
Test

22859 Posts

Posted - 2004-10-22 : 09:57:12

"run the INFORMATION_SCHEMA query without the "and TABLE_NAME='SERVER' in Query Analyzer. Should return all identity columns of the database you´re connected to with the respective tables..."

I don't see any indication of Identity column - or am I being thick?

Kristen

tsturm
Starting Member

7 Posts

Posted - 2004-10-25 : 04:25:19

@Kristen: I think the answer to your question is NO. ;-)
But it could be something in the server settings - which bodes not well for my table viewer in terms of compatibility... :-(
What I get when I select the "master" db in Query Analyzer an run:
select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
is:
COLUMN_NAME TABLE_NAME
id dtproperties

cheers -
Tom.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-25 : 04:41:38

tsturm,
I would say that is the expected result when you run that query in the master database.
Never had any problems with the columnproperty function.

select so.name as table_name, sc.name as identity_column
from sysobjects so join syscolumns sc on so.id = sc.id
where colstat & 1 = 1

-- or this

select so.name as table_name, sc.name as identity_column
from sysobjects so join syscolumns sc on so.id = sc.id
where sc.status & 0x80 = 0x80

but i recommend COLUMNPROPERTY...

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-25 : 05:00:46

the code works, i ran it on one of the databases, and it showed me the fields which are identity.

--------------------
keeping it simple...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-25 : 05:02:34

Of course it works

Kristen
Test

22859 Posts

Posted - 2004-10-25 : 08:00:03

"i ran it on one of the databases"

An Oracle database, for example?

Kristen

tsturm
Starting Member

7 Posts

Posted - 2004-10-29 : 06:36:42

@rockmoose: yup, it can be done with select...from sysobjects - like many other things I´ve got to do...unfortunately, sysobjects, syscolumns and the like are MS SQL Server specific. While I have to write provider specific code, I want to stick to as much generic code as possible. Now, INFORMATION_SCHEMA queries *should* work on all SQL92 compliant providers - that´s why Kriten asked "Oracle anyone?".
I would be really happy if anyone gets to run the query below on anything OTHER than MS SQL. Likewise, if it didn´t it´d be a major bummer

- it would also kind of imply that SQL is about what HTML used to be 5 years ago: when browser independent pages made you look like

cheers -
Tom

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 07:22:24

Yes,
That's why I said:

quote:
You should have different Metadata scripts for each database you are trying to support.

Different database vendors all have different things that they support/not support, comply/not comply
according to any given SQL Standard ( not to mention datatypes ).

Either you choose to support a subset of metadata that all your supported SQL database servers handle equally.
Or You will have to implement vendor specific things in your scripts/code.

You could decide on a specific set of metadata thet your code supports,
then you write Vendor specific metadata scripts to retrieve that metadata,
if a vendor doesn't support a metadata attribute that You support - then it is N/A ( not applicable for that vendor )
and vice versa - If a Vendor supports something that you don't want - then just don't bother with that.

Alternatively You could ask the DATA PROVIDER that you are using for metadata. ( but you still have to decide which metadata you want ).

You need to make your own DataModel of your MetaData anyway,
and import into that DataModel whatever you can from the different Vendors.

Best of Luck

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 08:18:29

BTW,
Since this is primarily a MS SQL Server ng I might add

select
	object_name(id) as table_name,
	name as column_name,
	ident_seed(object_name(id)) as seed,
	ident_incr(object_name(id)) as increment,
	ident_current(object_name(id)) as last_identity
from
	syscolumns
where	-- 4 ways to get identity ....
	autoval is not null
	and colstat & 1 = 1
	and status & 0x80 = 0x80
	and columnproperty(id,name,'IsIdentity') = 1

And BTW, the inability to get Identity property from INFORMATION_SCHEMA is really a bummer...

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources