How to get column names, dataype information in SQL

 To get column names, datatypes and its maximum length use below code

SELECT C.NAME AS COLUMN_NAME,

TYPE_NAME(C.USER_TYPE_ID) AS DATA_TYPE,

C.MAX_LENGTH

FROM SYS.COLUMNS C

JOIN SYS.TYPES T

ON C.USER_TYPE_ID=T.USER_TYPE_ID

WHERE C.OBJECT_ID=OBJECT_ID('[pce].[Product_Fact]');