SQL Server queries the basic information of a column, including the table name, column name, data type, remarks, maximum width, increment, empty, and primary key.

Select B. Name as tablename, A. Name columnname, C. Name as datatype, P. value as description, A. max_length as maxlength
, (Case A. is_identity when 1 then 'true' else 'false' end) as [identity]
, (Case A. is_nullable when 1 then 'true' else' false' end) as [isnull]
, (Case K. type when 'pk' then 'true' else 'false' end) as [iskey]
From SYS. Objects B
Inner join SYS. columns A on A. object_id = B. object_id
Left join SYS. Types C on A. system_type_id = C. user_type_id
Left join SYS. extended_properties P on p. major_id = A. object_id and P. minor_id = A. column_id and P. Name = 'Ms _ description'
Left join information_schema.constraint_column_usage SCC on SCC. table_name = B. Name and SCC. column_name = A. Name
Left join SYS. Objects K on K. Name = SCC. constraint_name and K. type = 'pk'
Where B. type = 'U' and A. Name = 'id'