Obtain the field information of the M $ SQL Server user table

1 Use Database1
2 Select
3 Table Name =   Case A. colorder When   1   Then C. Name Else   ''   End ,
4 Collation = A. colorder,
5 Field name = A. Name,
6 Identifier =   Case   Columnproperty (A. ID, A. Name, ' Isidentity ' ) When   1   Then   ' √ '   Else   ''   End ,
7 Primary Key =   Case
8 When   Exists (
9 Select   *
10 From Sysobjects
11 Where Xtype = ' PK '   And Name In (
12 Select Name
13 From Sysindexes
14 Where ID = A. ID And Indid In (
15 Select Indid
16 From Sysindexkeys
17 Where ID = A. ID And Colid In (
18 Select Colid
19 From Syscolumns
20 Where ID = A. ID And Name = A. Name
21 )
22 )
23 )
24 )
25 Then   ' √ '
26 Else   ''
27 End ,
28 Type = B. Name,
29 Bytes = A. length,
30 Length =   Columnproperty (A. ID, A. Name, ' Precision ' ),
31 Decimal =   Case   Isnull ( Columnproperty (A. ID, A. Name, ' Scale ' ), 0 )
32 When   0   Then   ''
33 Else   Cast ( Columnproperty (A. ID, A. Name, ' Scale ' ) As   Varchar )
34 End ,
35 Null allowed =   Case A. isnullable When   1   Then   ' √ '   Else   ''   End ,
36 Default Value =   Isnull (D. [ Text ] , '' ),
37 Description =   Isnull (E. [ Value ] , '' )
38 From Syscolumns
39 Left Join Policypes B On A. xtype = B. xusertype
40 Inner   Join Sysobjects C On A. ID = C. ID And C. xtype = ' U '   And C. Name <> ' Dtproperties '
41 Left Join Syscomments d On A. cdefault = D. id
42 Left Join Sysproperties E On A. ID = E. ID And A. colid = E. smallid
43 Order   By C. Name, A. colorder