Search this blog

Thursday, June 11, 2009

Lists Identity columns available in a Database

The Following Query lists the Table_Name and column names, which are all idenity field from a current database


SELECT

object_name(SysObj.object_id) AS Table_Name,

SysCols.name as Identity_Col_Name

FROM

sys.columns SysCols,

sys.objects SysObj

WHERE

SysCols.object_id = SysObj.object_id and

is_identity = 1 and

SysObj.type IN ('U')


Sample OutPut of this Query:

Table_Name

Identity_Col_Name

DIM_AFFILIATE_INDICATOR

AFFILIATE_INDICATOR_ID

DIM_ALBUM

ALBUM_ID

DIM_ALBUM_BRONZE

ALBUM_ID

DIM_ALBUM_FN

ALBUM_ID

DIM_ALBUM_PCAT

ALBUM_ID

DIM_ARTIST

ARTIST_ID

DIM_BG_INDICATOR

BG_INDICATOR_ID

DIM_CATALOG

CATALOG_ID

DIM_CATALOG_NO

CATALOG_ID

DIM_CATALOG_NO_FN

CATALOG_ID

DIM_CATALOG_PCAT

CATALOG_ID

DIM_CHECK_NO

CHECK_ID

Dim_Class

ClassID

No comments:

Post a Comment