Get the ColumnName of the Identity in a Table

Hi,
I need to know the name of the Column which is the Identity of a Table.
Below is the query to what point I succeeded. But now I'm stuck.
This query returns all tables with an Identity-Column in a database, crossed
with ALL columns (obviously).
Any suggestions?
TIA,
Michael
SELECT INFORMATION_SCHEMA.TABLES.TABLE_CATALOG,
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
INFORMATION_SCHEMA.TABLES.TABLE_NAME,
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
IDENT_SEED(INFORMATION_SCHEMA.TABLES.TABLE_NAME) AS
IDENT_SEED, IDENT_INCR(INFORMATION_SCHEMA.TABLES.TABLE_NAME)
AS IDENT_INCR,
IDENT_CURRENT(INFORMATION_SCHEMA.TABLES.TABLE_NAME) AS IDENT_CURRENT
FROM INFORMATION_SCHEMA.TABLES INNER JOIN
INFORMATION_SCHEMA.COLUMNS ON
INFORMATION_SCHEMA.TABLES.TABLE_CATALOG =
INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG AND
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA =
INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA AND
INFORMATION_SCHEMA.TABLES.TABLE_NAME =
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE (IDENT_SEED(INFORMATION_SCHEMA.TABLES.TABLE_NAME) IS NOT NULL)I think just found it:
SELECT TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
IDENT_SEED(TABLE_NAME) AS IDENT_SEED,
IDENT_INCR(TABLE_NAME) AS IDENT_INCR,
IDENT_CURRENT(TABLE_NAME) AS IDENT_CURRENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COL
UMN_NAME,'IsIdentity')=1
I'm just not sure if this approach will work on Server 2005...
Michael|||How about this?
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
AND TABLE_NAME = 'Orders'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael Maes" <michael@.merlot.com> wrote in message
news:262D812C-5439-49F0-B6CA-C2B4511BF0FD@.microsoft.com...
> Hi,
> I need to know the name of the Column which is the Identity of a Table.
> Below is the query to what point I succeeded. But now I'm stuck.
> This query returns all tables with an Identity-Column in a database, cross
ed
> with ALL columns (obviously).
> Any suggestions?
> TIA,
> Michael
> SELECT INFORMATION_SCHEMA.TABLES.TABLE_CATALOG,
> INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
> INFORMATION_SCHEMA.TABLES.TABLE_NAME,
> INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
> IDENT_SEED(INFORMATION_SCHEMA.TABLES.TABLE_NAME) AS
> IDENT_SEED, IDENT_INCR(INFORMATION_SCHEMA.TABLES.TABLE_NAME)
> AS IDENT_INCR,
> IDENT_CURRENT(INFORMATION_SCHEMA.TABLES.TABLE_NAME) AS IDENT_CURRENT
> FROM INFORMATION_SCHEMA.TABLES INNER JOIN
> INFORMATION_SCHEMA.COLUMNS ON
> INFORMATION_SCHEMA.TABLES.TABLE_CATALOG =
> INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG AND
> INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA =
> INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA AND
> INFORMATION_SCHEMA.TABLES.TABLE_NAME =
> INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
> WHERE (IDENT_SEED(INFORMATION_SCHEMA.TABLES.TABLE_NAME) IS NOT NULL)|||Hi Tibor,
Thanks for your reply.
Will this approach also be compatible with 2005?
Regards,
Michael
"Tibor Karaszi" wrote:

> How about this?
> SELECT *
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
> AND TABLE_NAME = 'Orders'
>|||Yep. The purpose of info schema view is not only be stable across versions,
but also across
different DBMS vendors. They are defined in the ANSI SQL standard.
I just ran the query against 2005 April CTP, just to give it a spin...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael Maes" <michael@.merlot.com> wrote in message
news:B85C547B-726F-47A7-84FE-70A0384314CE@.microsoft.com...
> Hi Tibor,
> Thanks for your reply.
> Will this approach also be compatible with 2005?
> Regards,
> Michael
> "Tibor Karaszi" wrote:
>|||Thanks Tibor,
Quite a relief :-)
Michael
"Tibor Karaszi" wrote:

> Yep. The purpose of info schema view is not only be stable across versions
, but also across
> different DBMS vendors. They are defined in the ANSI SQL standard.
> I just ran the query against 2005 April CTP, just to give it a spin...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>