How to know which fields are identity?

Hi. I need to know if a table has identity fields. Is there any view to get
that information?
Regards,
Diego F.SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)),
column_name,'IsIdentity')=1 ;
David Portas
SQL Server MVP
--|||Thanks a lot!
Regards,
Diego F.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escribi en el
mensaje news:1123150974.908211.221990@.g14g2000cwa.googlegroups.com...
> SELECT table_schema, table_name, column_name
> FROM information_schema.columns
> WHERE COLUMNPROPERTY(OBJECT_ID(
> QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)),
> column_name,'IsIdentity')=1 ;
> --
> David Portas
> SQL Server MVP
> --
>|||I found a problem with that. It doesn't work if I try to use a table from
other database. Is there another possibility?
Regards,
Diego F.
"Diego F." <diegofrNO@.terra.es> escribi en el mensaje
news:OGuMt9NmFHA.3120@.TK2MSFTNGP09.phx.gbl...
> Thanks a lot!
> --
> Regards,
> Diego F.
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escribi en el
> mensaje news:1123150974.908211.221990@.g14g2000cwa.googlegroups.com...
>|||SELECT
U.name AS table_schema,
T.name AS table_name,
C.name AS column_name
FROM database_name.dbo.syscolumns AS C
JOIN database_name.dbo.sysobjects AS T
ON C.id = T.id
JOIN database_name.dbo.sysusers AS U
ON T.uid = U.uid
WHERE C.status & 0x80 = 0x80 ;
David Portas
SQL Server MVP
--|||Type 'USE AnotherDatabaseName' before that SELECT command.
Or 'AnotherDatabaseName.information_schema.columns' instead of 'information_
schema.columns'.
Diego F. wrote:
> I found a problem with that. It doesn't work if I try to use a table from
> other database. Is there another possibility?
> --
> Regards,
> Diego F.
>
> "Diego F." <diegofrNO@.terra.es> escribi en el mensaje
> news:OGuMt9NmFHA.3120@.TK2MSFTNGP09.phx.gbl...
>
>|||> 'AnotherDatabaseName.informati=ADon_schema.columns' instead of 'informati=
on_schema.columns'.
That gets you the column names but unfortunately the metadata functions
I used are scoped to the current DB so the WHERE clause won't find the
correct columns.
--=20
David Portas=20
SQL Server MVP=20
--|||David Portas wrote:
>
> That gets you the column names but unfortunately the metadata functions
> I used are scoped to the current DB so the WHERE clause won't find the
> correct columns.
Ah, you are right indeed.|||Thank you all. I'll try to deal with that.
Regards,
Diego F.
"Sericinus hunter" <serhunt@.flash.net> escribi en el mensaje
news:VarIe.953$646.265@.newssvr22.news.prodigy.net...
> David Portas wrote:
> Ah, you are right indeed.