sql : get table column information using sql query also with the help of system table

SELECT sc.name 'ColumnName',st.Name 'DataType',
sc.max_length 'Length',
(case when sc.is_nullable='True' then 'Yes' else 'No' end) as 'Nullable',
ISNULL(i.is_primary_key, 0) 'PrimaryKeyStatus'
FROM sys.columns sc
INNER JOIN sys.types st ON sc.user_type_id = st.user_type_id
LEFT OUTER JOIN sys.index_columns sic  ON sic.object_id = sc.object_id  AND sic.column_id = sc.column_id
LEFT OUTER JOIN sys.indexes i ON sic.object_id = i.object_id AND sic.index_id = i.index_id
WHERE sc.object_id = OBJECT_ID('YourTableName')