FROM sys.columns AS c 
 INNER JOIN sysobjects so
    ON c.object_id = so.id

CASE WHEN c.name IN (
        SELECT c.name
          FROM sysindexes i
          JOIN sysobjects o 
            ON i.id = o.id
          JOIN sysobjects pk 
            ON i.name = pk.name
           AND pk.parent_obj = i.id
           AND pk.xtype = 'PK'
          JOIN sysindexkeys ik 
            ON i.id = ik.id
           AND i.indid = ik.indid
            JOIN syscolumns c 
            ON ik.id = c.id
           AND ik.colid = c.colid
         WHERE o.name = so.name) THEN 'Y'
    ELSE ''
END AS 'PK'

SELECT DISTINCT 
 t.name AS Table_Nme, ept.value AS Table_Desc, c.name AS Column_Nme, 
 st.name + '(' + CASE WHEN c.max_length = - 1 THEN 'max' ELSE CAST(c.max_length AS varchar(100)) END + ')' AS Column_Data_Type, 
 CASE WHEN c.is_nullable = 0 THEN 'False' ELSE 'True' END AS Null_Allowed_Ind, epc.value AS Column_Desc, 
 CASE WHEN dc.definition LIKE '(getdate())' THEN 'Current Date' ELSE dc.definition END AS Column_Default_Value, 
 CASE WHEN PrimaryKeyColumns.CONSTRAINT_NAME IS NOT null THEN 'Yes' ELSE '' END AS Primary_Key_Ind,  
 CASE WHEN t .object_id = fk.parent_object_id AND 
 c.column_id = fk.parent_column_id THEN 'Yes' ELSE '' END AS Foriegn_Key_Ind, CASE WHEN c.is_identity = 1 THEN 'Yes' ELSE '' END AS Identity_Column_Ind, 
 ft.name AS Foreign_Table, c.column_id
FROM sys.columns AS c INNER JOIN
 sys.systypes AS st ON st.xtype = c.user_type_id LEFT OUTER JOIN
 sys.extended_properties AS epc ON epc.major_id = c.object_id AND epc.minor_id = c.column_id LEFT OUTER JOIN
 sys.default_constraints AS dc ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id INNER JOIN
 sys.tables AS t ON c.object_id = t.object_id LEFT OUTER JOIN
 sys.extended_properties AS ept ON ept.major_id = t.object_id AND ept.minor_id = t.parent_object_id LEFT OUTER JOIN
 sys.key_constraints AS pk ON t.object_id = pk.parent_object_id LEFT OUTER JOIN
 sys.foreign_key_columns AS fk ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id LEFT OUTER JOIN
 sys.tables AS ft ON fk.referenced_object_id = ft.OBJECT_ID
 --start added by hjm
 LEFT OUTER JOIN (
	 SELECT  
		Tab.TABLE_NAME,  
		Tab.CONSTRAINT_NAME,  
		K.COLUMN_NAME,  
		K.ORDINAL_POSITION  
	FROM  
		INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab 
		INNER JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE K 
		ON Tab.CONSTRAINT_NAME = K.CONSTRAINT_NAME  
	WHERE 
		Tab.CONSTRAINT_TYPE = 'PRIMARY KEY'  
    ) AS PrimaryKeyColumns ON (PrimaryKeyColumns.COLUMN_NAME = c.name AND t.NAME = PrimaryKeyColumns.TABLE_NAME )
 --hjm end
WHERE (t.name NOT IN ('sysdiagrams', 'DataDictionary')) AND (st.name NOT LIKE '%sysname%')
ORDER BY Table_Nme, c.column_id