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
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)
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 BJECT_ID = c.OBJECT_IDAND C.is_identity= 1 AND BJECT_ID = OBJECT_ID(@TableName)JOIN SYS.TYPES AS t ON c.user_type_id=t.user_type_id