SELECT 

 T.type_table_object_id

 ,T.NAME [TABLE NAME]

 ,SCH.NAME [SCHEMA NAME]

 ,C.COLUMN_ID

 ,C.NAME [COLUMN NAME]

 ,UPPER(TYP.NAME) + ISNULL(CASE 

WHEN TYP.NAME IN ('NVARCHAR', 'NTEXT', 'NCHAR') 

THEN '(' + CASE C.MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CAST(C.MAX_LENGTH / 2 AS NVARCHAR) END + ')'

WHEN TYP.NAME IN ('VARCHAR', 'TEXT', 'CHAR', 'BINARY', 'VARBINARY')

THEN '(' + CASE C.MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CAST(C.MAX_LENGTH AS NVARCHAR) END + ')'

WHEN TYP.NAME IN ('DATETIME2', 'DATETIMEOFFSET', 'TIME')

THEN '(' + CAST(C.SCALE AS NVARCHAR) + ')'

WHEN TYP.NAME IN ('DECIMAL', 'NUMERIC')

THEN '(' + CAST(C.PRECISION AS NVARCHAR) + ', ' + CAST(C.SCALE AS NVARCHAR) + ')'

END, '') TYPE

 ,CASE C.IS_NULLABLE WHEN 1 THEN 'NULLABLE' ELSE 'NOT NULL' END NULLABLE

  ,ISNULL(CASE 

WHEN TYP.NAME IN ('NVARCHAR', 'NTEXT', 'NCHAR') 

THEN 'String'

WHEN TYP.NAME IN ('VARCHAR', 'TEXT', 'CHAR')

THEN 'String'

WHEN TYP.NAME IN ('BINARY', 'VARBINARY')

THEN 'Byte[]'

WHEN TYP.NAME IN ('DATETIME2', 'DATETIMEOFFSET', 'TIME', 'DATETIME')

THEN 'DateTime'

WHEN TYP.NAME IN ('DECIMAL', 'NUMERIC')

THEN 'Double'

WHEN TYP.NAME IN ('SQL_VARIANT')

THEN 'Object'

WHEN TYP.NAME IN ('FSUDT.ACTPRODUCT')

THEN 'System.Numerics.BigInteger'

WHEN TYP.NAME IN ('INT')

THEN 'Int32'

WHEN TYP.NAME IN ('BIGINT')

THEN 'Int64'

WHEN TYP.NAME IN ('BIT')

THEN 'Bool'

WHEN TYP.NAME IN ('SMALLINT')

THEN 'Byte'

END, '') [TYPE.NET]

 

 ,ISNULL(KC.TYPE, '') PK

 ,ISNULL(KC2.NAME, '') UK

 --,CASE 

--WHEN INDCOL.IS_DESCENDING_KEY IS NULL THEN '' 

--WHEN INDCOL.IS_DESCENDING_KEY = 1 THEN '1' 

--ELSE '0' END  [DESC]

 --,CASE ISNULL(FKC.CONSTRAINT_COLUMN_ID, '') WHEN 0 THEN '' ELSE 'FK' END FK

 --,ISNULL(SCHEMA_NAME(REF.SCHEMA_ID), '') FK_REF_TBLSCH

 --,ISNULL(REF.NAME, '') FK_REF_TBL

 --,ISNULL(REFCOL.NAME, '') FK_REF_TBLCOL

 ,CASE C.IS_IDENTITY 

WHEN 1 THEN 'IDENTITY(' + 

CAST(IC.SEED_VALUE AS NVARCHAR) + ', ' + 

CAST(IC.INCREMENT_VALUE AS NVARCHAR) + ')'  

ELSE '' END [IDENTITY]

 ,C.IS_NULLABLE

 

 ,C.MAX_LENGTH

 ,C.PRECISION

 ,C.SCALE

 ,C.USER_TYPE_ID

 ,C.SYSTEM_TYPE_ID

 ,C.IS_COMPUTED

--SELECT *

FROM 

 SYS.ALL_COLUMNS C 

INNER JOIN SYS.table_types T

ON C.OBJECT_ID = T.type_table_object_id

INNER JOIN SYS.SCHEMAS SCH

ON T.SCHEMA_ID = SCH.SCHEMA_ID

INNER JOIN SYS.TYPES TYP

ON C.USER_TYPE_ID = TYP.USER_TYPE_ID

LEFT JOIN SYS.IDENTITY_COLUMNS IC

ON IC.OBJECT_ID = C.OBJECT_ID 

AND IC.COLUMN_ID = C.COLUMN_ID

LEFT JOIN SYS.INDEX_COLUMNS INDCOL

ON INDCOL.OBJECT_ID = T.type_table_object_id

AND INDCOL.COLUMN_ID = C.COLUMN_ID

--AND INDCOL.INDEX_ID = 1

LEFT JOIN SYS.INDEX_COLUMNS INDCOL2

ON INDCOL2.OBJECT_ID = T.type_table_object_id

AND C.COLUMN_ID = INDCOL2.COLUMN_ID

LEFT JOIN SYS.KEY_CONSTRAINTS KC 

ON T.type_table_object_id = KC.PARENT_OBJECT_ID

AND INDCOL2.INDEX_ID = KC.UNIQUE_INDEX_ID

AND KC.TYPE = 'PK'

LEFT JOIN SYS.INDEX_COLUMNS INDCOL3

ON INDCOL3.OBJECT_ID = T.type_table_object_id

AND C.COLUMN_ID = INDCOL3.COLUMN_ID

LEFT JOIN SYS.KEY_CONSTRAINTS KC2

ON T.type_table_object_id = KC2.PARENT_OBJECT_ID

AND INDCOL3.INDEX_ID = KC2.UNIQUE_INDEX_ID

AND KC2.TYPE = 'UQ'

--WHERE T.NAME ='SYSTEMCOREACCOUNT'

ORDER BY [TABLE NAME], column_id