Obtain the database name, all table names, all field names, and column descriptions in the SQL database.

1. Get allDatabase Name: 
(1) Select name from Master. DBO. sysdatabases order by name
2. Get all table names:
(1) Select name from sysobjects where xtype = 'U' order by name
Xtype = 'U': indicates all user tables;
Xtype = 's': indicates all system tables;

(2) Select name from sysobjects where type = 'U' and sysstat = '83'

Note: Generally, only type = 'U' is required, but sometimes the system tables are mixed in (I don't know why). After adding the following sentence, these system tables can be deleted.

3. Get all field names: 
(1) Select name from syscolumns where id = object_id ('tablename ')

(2) Select syscolumns. name, policypes. name, syscolumns. isnullable, syscolumns. length from syscolumns, policypes where syscolumns. xusertype = policypes. xusertype and "syscolumns. id = object_id ('tablename ')

Note:
(A) In order to highlight some important content, several pieces of information are selected for output.
(B) The syscolumns table only contains data type numbers. To obtain the complete name, you need to find it from the categorypes table. Generally, it is better to use xusertype for user data types, there will be no one-to-many cases.
(C) syscolumns. length is the length of the physical memory, so nvarchar, varchar, and Other types are shown in the database as half of this.

4. Obtain the column names of the primary keys in the table:

Select syscolumns. name from syscolumns, sysobjects, sysindexes, sysindexkeys where syscolumns. id = object_id ('tablename') and sysobjects. xtype = 'pk' and sysobjects. parent_obj = syscolumns. ID and sysindexes. id = syscolumns. ID and sysobjects. name = sysindexes. name and sysindexkeys. id = syscolumns. ID and sysindexkeys. indid = sysindexes. indid and syscolumns. colid = sysindexkeys. colid

Note: This is found in four system tables. The relationship is complex and can be roughly expressed:
Syscolumns contains the column information and table ID in the table. The sysobjects table contains the primary key name (similar to pk_table) and Table ID. The sysindexes table contains the primary key name, table ID, and index number, sysindexkeys contains the table ID, index number, and column number. After one item is matched, the column name can be found ~

5. Get the description of the columns in the table:

Select. name, G. value From syscolumns as a left join sysproperties g on. id = G. ID and. colid = G. smallid where. id = 'table id'