MS SQL Server searches for the column name of the primary key for a table

Original: MS SQL Server searches for the column name of a table's primary key

SELECT syscolumns. Name
from syscolumns,SYSOBJECTS,sysindexes,Sysindexkeys
WHERE syscolumns. ID =object_id(' tab_xxx ')--syscolumns.id The Table object ID that the column belongs to
andSYSOBJECTS. Xtype =' PK ' --sysobjects.xtype Object Type
andSYSOBJECTS. Parent_obj =syscolumns. ID
andsysindexes. ID =syscolumns. ID
andSYSOBJECTS. Name =sysindexes. Name
andSysindexkeys. ID =syscolumns. ID
andSysindexkeys. indid =sysindexes. indid--the same column of the same table, possibly with different types of indexes
andsyscolumns. colid =Sysindexkeys. colid

Note: This is found in 4 system tables, and the relationship is more complex and can be expressed roughly as:
　　The columns in the syscolumns table colid and table id,sysobjects have primary key names (that is, pk_table-like) and table id,sysindexes have primary key names and table IDs and index numbers. The table ID and index number and column number are stored in the Sysindexkeys, and a column name is found when one item is matched.

SELECT * from syscolumns
--syscolumns each column in a table and view occupies a row in the table, and each parameter in the stored procedure also occupies a row in the table.
--syscolumns.id The Table object ID that the column belongs to
--syscolumns.colid for this Column object ID

SELECT * from SYSOBJECTS
--sysobjects a row in the table for each object (constraints, default values, logs, rules, stored procedures, tables, views, and so on) created within the database.
--sysobjects.xtype Object Type
--sysobjects.parent_obj the object identification number of the parent object (for example, for a trigger or constraint, which is the table ID)
--sysobjects.name object name, here primary key name (i.e. pk_table similar)

SELECT * FROM sysindexes
--Each index and table in the database takes one row in the table.
--sysindexes.id table ID (if indid = 0 or 255). Otherwise, the index belongs to the table ID.
--sysindexes.name table name (if indid = 0 or 255). Otherwise, the name of the index. Here is the primary key name (primary key must be indexed, see analysis below)
--sysindexes.indid type of index ID

SELECT * from Sysindexkeys
--Each index and table in the database takes one row in the table.
--sysindexkeys.id table ID
--sysindexkeys.indid type of index ID
--sysindexkeys.colid Column ID

The primary key must be a unique index, and the uniqueness index is not necessarily the primary key

A primary key is a property or group of properties that uniquely identifies a row in a table, and a table can have only one primary key, but there may be multiple candidate indexes. Because a primary key uniquely identifies a row of records, you can ensure that you do not have pigtailed errors when you perform data updates and deletions. The primary key, in addition to the above, often forms referential integrity constraints with foreign keys to prevent inconsistent data. Database at design time, the primary key plays a very important role.

The primary key guarantees that the record is unique and the primary key domain is not empty, and the database management system automatically generates a unique index for the primary key, so the primary key is also a special index.

MS SQL Server searches for the column name of the primary key for a table