Select Identity

[Roger Jordan]

Hi there,

I need to get the information from a sql 2005 database if a column of a
table is an IDENTITY column.

Any idea how to get this information by SELECT ...?

Seem that is noch part of INFORMATION_SCHEMA.COLUMNS.

Regards,
Roger

[Tom]

On Aug 26, 4:04 pm, "Roger Jordan" <roger.jor...@gfktrustmark.ch>
wrote:

SELECT columns.is_identity
FROM sys.objects
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
JOIN sys.columns on objects.object_id = columns.object_id
WHERE objects.name = 'TABLENAME'
AND schemas.name = 'SCHEMANAME' -- LIKELY TO BE DBO
AND columns.name = 'COLUMNNAME'

[Erland Sommarskog]

Roger Jordan (roger....@gfktrustmark.ch) writes:
> I need to get the information from a sql 2005 database if a column of a
> table is an IDENTITY column.
>
> Any idea how to get this information by SELECT ...?
>
> Seem that is noch part of INFORMATION_SCHEMA.COLUMNS.

INFORMATION_SCHEMA only covers ANSI-compliable features.

Two more ways in addition to what Tom posted:

SELECT objectpropertyex(object_id('tbl'), 'TableHasIdentity) = 1

SELECT ident_current('tbl') IS NOT NULL

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx