SQL Server obtains the data of a table column.

To obtain the data of the columns in a table, you must first understand the five system tables in sqlserver.
Sysobjects, syscolumns, policypes, sysindexes, sysindexkeys

Sysobjects, each database has a system table that records all database objects. For example, we can obtain the primary key and table object name from it.
Sysindexes, each database has a system table that records the indexes of all tables, including the index object name.
Sysindexkeys, each database has a system table that records all the indexes and corresponding columns.
Syscolumns, each database has such a system table, which records the columns of all tables. We can obtain the sequence number, name, and whether the column can be empty from the table.
Systypes, each database has a system table that records all data types. We can obtain the name of a column of data type in the table, such as varchar, int, etc.

Sysobjects contains an xtype field, char (2) type, which records the object type. For example, PK is the primary key, S is the user table, and P is the stored procedure, both are in uppercase, and there is a name field that records the object name.
Sysindexes has a name field, which records the name of the object, and an indid field, which records the index number.
In sysindexkeys, there is an indid field, which records the index number, a colid, and a column number.
Syscolumns also has an xtype field of the int type, but it records the Data Type of this column.Code, Some numbers. These numbers correspond to the xtype in the policypes table, so that the data type name can be obtained from the policypes table. There is also a colid field that records the column number, there is also a Name field, which records the column name, And the colorder field records the column number.
Policypes contains an xtype and INT type, which records the code of the data type in this column, and a name field, which records the data type name.

In syscolumns, sysindexes, and sysindexkeys, the ID field corresponds to the ID of the table to which the table belongs, rather than the ID of your own data.
The data in syscolumns, sysindexes, and sysindexkeys does not have uniqueness. Because all tables share one system table, colid, indid, and so on are only unique when the table name is different.

In this way, based on the above analysis, we can get the specific information of a table field.
Obtain the column number and column name from syscolumns. Can it be blank?
Obtain the data type from syscolumns and policypes.
Obtain the primary key information from all five tables.
We can see that there are many codes used to obtain the primary key!

Select a. colorder number,
A. Name columnname,
B. Name type,
(Case when a. isnullable = 1 then 'y' else' end) allownull,
(Case when
(Select count (*) from sysobjects where name in
(Select name from sysindexes where id = A. ID and indid in
(Select indid from sysindexkeys where id = A. ID and colid in
(Select colid from syscolumns where id = A. ID and name = A. Name )))
And xtype = 'pk')> 0 then 'y' else 'n' end) isprimarykey
From syscolumns a left join policypes B on A. xtype = B. xusertype
Where a. ID = object_id ('B') order by A. colorder