SQL Experience Sharing (2) obtaining all the table names, field names, and tables in the database

2.1 obtain the names of all tables in the database.

Sample Data:

Statement:

Select T. Name 'table name' from sysobjects t where objectproperty (T. ID,
N 'isusertable') = 1

Or use select name from sysobjects where type = 'U'

Execution result:

2.2 obtain the names of all fields in all tables

Statement:

Select distinct C. Name 'field name'From sysobjects
T, syscolumns C
Where T. ID = C. ID 
AndObjectproperty (T. ID, n'isusertable') = 1

Execution result:

Note: There are many fields, which are omitted below

 

2.3 query all tables and fields

Statement: Select T. Name 'table name', C. name' field name'From
Sysobjects T, syscolumns C
Where T. ID = C. ID 
AndObjectproperty (T. ID, n'isusertable') =
1Group by T. Name, C. Name

Execution result:

Note: There are many fields, which are omitted below

 

2.4 query all fields in a table

Original table_1 data:

Statement:
Select T. Name, C. Name 'field name'From sysobjects t,
Syscolumns C
Where T. ID = C. ID 
AndObjectproperty (T. ID, n'isusertable') =
1
AndT. Name = 'table _ 1'

Execution result:

 

2.5 check which table the field belongs to (that is, find the table containing the same field)

Queries which tables have name Columns

Statement:

Select distinct T. name from sysobjects T, syscolumns C
Where T. ID = C. ID 
AndObjectproperty (T. ID, n'isusertable') =
1
And C. Name in ('name ')

Execution result: