SELECT A.TABLE_NAME,A.ORDINAL_POSITION,A.COLUMN_NAME,A.DATA_TYPE,A.CHARACTER_MAXIMUM_LENGTH,A.IS_NULLABLE
FROM (
select
TABLE_NAME,
ORDINAL_POSITION ,
COLUMN_NAME,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE
--COLUMN_DEFAULT
--TABKE_CATALOG
from INFORMATION_SCHEMA.COLUMNS C
INNER JOIN SYS.tables D
ON C.TABLE_NAME = D.name
) A
LEFT OUTER JOIN (
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
) B
ON A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME = B.TABLE_NAME
AND TABLE_CATALOG = DB_NAME(DB_ID())
ORDER BY A.TABLE_NAME,A.ORDINAL_POSITION
GO