SQL Server - All Columns with type and key usage

gistfile1.sql

SELECT c.TABLE_NAME, c.COLUMN_NAME, kcu.CONSTRAINT_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, tc.CONSTRAINT_TYPE, 
	CASE tc.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN CAST(1 AS BIt) ELSE CAST(0 AS Bit) END AS IsPrimaryKey, 
	CASE (COLUMNPROPERTY(object_id(tc.TABLE_NAME), kcu.COLUMN_NAME, 'IsIdentity')) WHEN 1 THEN CAST(1 AS BIT) ELSE CAST(0 AS Bit) END as IsAuto
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
	ON c.TABLE_SCHEMA = kcu.CONSTRAINT_SCHEMA AND c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
	ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME