SQL Server Performance

Get Primary, Foreign, Unique and Identity Key ColumnName

Discussion in 'SQL Server 2008 General Developer Questions' started by baburk, Aug 6, 2010.

  1. baburk New Member

    Hi all,
    I wants to get the PrimaryKey, ForeignKey, UniqueKey, IdentityKey column names. I tried with the below query. But it returns all the ColumnName in the table.
    DECLARE @TableName nVarchar(256)
    SET @TableName = 'PkFkIkTest'
    SELECT DISTINCT col.COLUMN_NAME, col.DATA_TYPE, col.TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblCons
    INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col ON col.TABLE_NAME = tblCons.TABLE_NAME AND tblCons.TABLE_NAME = @TableName
    WHERE
    tblCons.CONSTRAINT_TYPE = 'PRIMARY KEY'
    OR tblCons.CONSTRAINT_TYPE = 'FOREIGN KEY'
    OR tblCons.CONSTRAINT_TYPE = 'UNIQUE'
    OR COLUMNPROPERTY (OBJECT_ID(col.Table_Name), col.Column_Name,'IsIdentity') = 1
    Thanks,
    Babu Kumarasamy
  2. FrankKalis Moderator

    You have to use another INFORMATION_SCHEMA in between your JOINs. Something like this (untested):
    SELECT DISTINCT col.COLUMN_NAME, C.DATA_TYPE, col.TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblCons
    JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS col ON tblCons.CONSTRAINT_NAME = col.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON col.TABLE_NAME = C.TABLE_NAME AND col.COLUMN_NAME = C.COLUMN_NAME
    WHERE
    tblCons.TABLE_NAME = @TableName AND
    (tblCons.CONSTRAINT_TYPE = 'PRIMARY KEY'
    OR tblCons.CONSTRAINT_TYPE = 'FOREIGN KEY'
    OR tblCons.CONSTRAINT_TYPE = 'UNIQUE'
    OR COLUMNPROPERTY (OBJECT_ID(col.Table_Name), col.Column_Name,'IsIdentity') = 1)

  3. baburk New Member

    Can't able to get the identity column name.
    So I changed like below.
    DECLARE
    @TableName NVARCHAR(50)SET
    @TableName = 'PkFkIkTest'SELECT
    col.COLUMN_NAME,C
    .DATA_TYPE ,col
    .TABLE_NAME, tblCons.CONSTRAINT_TYPEFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblCons
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS colON tblCons.CONSTRAINT_NAME = col.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.COLUMNS CON col.TABLE_NAME = C.TABLE_NAME
    AND col.COLUMN_NAME = C.COLUMN_NAMEWHERE
    tblCons.TABLE_NAME = @TableNameAND
    (
    --COLUMNPROPERTY (OBJECT_ID(colU.Table_Name), colU.Column_Name,'IsIdentity') = 1 ORtblCons
    .CONSTRAINT_TYPE = 'PRIMARY KEY'
    OR tblCons.CONSTRAINT_TYPE = 'FOREIGN KEY'
    OR tblCons.CONSTRAINT_TYPE = 'UNIQUE'
    )UNION
    SELECT
    c.name AS COLUMN_NAME, t.name as DATA_TYPE, @TableName as TABLE_NAME, 'IDENTITY' AS CONSTRAINT_TYPEFROM SYS.COLUMNS c
    INNER JOIN SYS.OBJECTS o
    ON :confused:BJECT_ID = c.OBJECT_IDAND C.is_identity= 1
    AND :confused:BJECT_ID = OBJECT_ID(@TableName)JOIN SYS.TYPES AS t ON c.user_type_id=t.user_type_id

Share This Page