SQL statements used to list information about all tables in the SQL Server database

1 Select  
2 ( Case   When A. colorder =   1   Then D. Name Else   ''   End ) Table Name,
3 A. Serial number of the colorder field,
4 A. Name field name,
5 ( Case   When   Columnproperty (A. ID, A. Name, ' Isidentity ' ) =   1   Then   ' √ '   Else   ''   End ) ID,
6 ( Case   When (
7 Select   Count ( * ) From Sysobjects
8 Where Name In (
9 Select Name From Sysindexes
10 Where (ID = A. ID) And (Indid In  
11 ( Select Indid From Sysindexkeys Where
12 (ID = A. ID) And (Colid In (
13 Select Colid From Syscolumns
14 Where (ID = A. ID) And (Name = A. Name ))))))
15 And (Xtype = ' PK ' )) >   0   Then   ' √ '   Else   ''   End ) Primary key,
16 B. Name type,
17 A. Length in bytes,
18 Columnproperty (A. ID, A. Name, ' Precision ' ) As Length,
19 Isnull ( Columnproperty (A. ID, A. Name, ' Scale ' ), 0 ) As Decimal places,
20 ( Case   When A. isnullable =   1   Then   ' √ '   Else   ''   End ) Can be empty,
21 Isnull (E. Text , '' ) Default value,
22 Isnull (G. [ Value ] , '' ) As Field description
23 From Syscolumns Left   Join Policypes B
24 On A. xtype = B. xusertype
25 Inner   Join Sysobjects d
26 On A. ID = D. id And D. xtype = ' U '   And D. Name <>   ' Dtproperties '
27 Left   Join Syscomments E
28 On A. cdefault = E. ID
29 Left   Join Sysproperties g
30 On A. ID = G. ID And A. colid = G. smallid
31 Order   By A. ID, A. colorder

What I learned from this SQL statement:
1. case when... then... else... end: When a SELECT statement is used in a select statement, the original descriptive information such as 0, 1 can be converted to the actual meaning insteadProgramAnd then judge based on the query results. This can be understood as simple data formatting. For example, case when a. isnullable = 1 then '√ 'else'' appears in this SQL statement to convert the database from to '√ 'and '';

2. Left join: This join method can be used to describe a join relation in the query results.

3. isnull function: isnull(Check_Expression, Replacement_value),The function is to replace null with the specified replacement value. For example, if the name of a book in the following SQL statement is null, the price is set to 0.00.

1 Select   Substring (Title, 1 , 15 ) As Title, Type As Type,
2 Isnull (Price, 0.00 ) As Price
3 From Titles
4

4. Of course, the most important thing is to learn about this list of database table information (including table name, field name, logo, primary key, field type, number of bytes, length, decimal places, allowed null, default value, Field description) SQL statement. Pai_^