SELECTC.name as [Column Name], T.name as [Data Type], C.max_length as [Max Length]C.Precision, C.scale, c.is_nullable as [is Nullable], c.is_identity as [is Identity], ISNULL(I.is_primary_key,0)[Primary Key], I.type_desc, Object_schema_name (FK.object_id)+ '.' + object_name(FK.object_id) + ' (' +Fk. Name+ ')' as [Foreign Key] fromsys.columns CINNER JOINSys.types T onc.system_type_id=t.system_type_id andc.user_type_id=t.user_type_id Left OUTER JOIN(Sys.index_columns ICINNER JOINSys.indexes I onIc.object_id =I.object_id andI.is_primary_key= 1 andic.index_id=i.index_id) onIc.object_id =C.object_id andic.column_id=c.column_id Left JOINSys.foreign_key_columns FKC onfkc.parent_object_id=C.object_id andfkc.parent_column_id=c.column_id Left JOINSys.columns FK onFk.object_id =fkc.referenced_object_id andfk.column_id=fkc.referenced_column_idWHEREC.object_id = object_id('Table Name')ORDER byc.column_id;
Get properties such as fields and primary keys in the MSSQL table SQL syntax