Query to get Primary Key & Foreign Key columns

#sql #database #sqlserver

I want to share this query for SQL Server to get PK, FK, and Data Type information from a DB Table.

	DECLARE @Schema NVARCHAR(MAX);
	DECLARE @Table NVARCHAR(MAX);

	SET @Schema = '';
	SET @Table = '';

	SELECT COLUMN_NAME AS ColumnName,IS_NULLABLE AS IsNullable
	,PrimaryKey =
	(SELECT 1
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
	ON TC.CONSTRAINT_TYPE IN ('PRIMARY KEY') AND
	TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND
	KU.TABLE_SCHEMA = @Schema AND
	KU.TABLE_NAME=@Table AND KU.COLUMN_NAME = C.COLUMN_NAME)

	,ForeignKey =
	(SELECT 1
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
	ON TC.CONSTRAINT_TYPE IN ('FOREIGN KEY') AND
	TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND
	KU.TABLE_SCHEMA = @Schema AND
	KU.TABLE_NAME=@Table AND KU.COLUMN_NAME = C.COLUMN_NAME)

	,DATA_TYPE AS DataType,CHARACTER_MAXIMUM_LENGTH AS CharacterLength

	FROM INFORMATION_SCHEMA.COLUMNS C
	WHERE TABLE_SCHEMA = @Schema AND TABLE_NAME = @Table
	ORDER BY ORDINAL_POSITION ASC