First, define the definition of the database object: The database object defines the structure of the database content. They are included in the database project, and the database project can also contain data generation plans and scripts.
Common database objects include: tables, indexes, views, charts, default values, rules, triggers, stored procedures, functions, etc.
1.sys.objects View
Each object created in the database corresponds to a row in the table, but does not include a DDL trigger, and the query trigger should use the Sys.triggers
Common different types of objects are distinguished in sys.objects:
type |
Type column |
type_desc column |
FOREIGN KEY constraint |
f |
foreign_key_constraint |
SQL scalar function |
F N |
sql_scalar_function |
SQL Inline table-valued function |
IF |
sql_inline_table_valued_funct ION |
internal table |
IT |
internal_table |
SQL stored procedure |
P |
sql_stored_procedure |
PRIMARY KEY constraint |
PK |
primary_key_constraint |
System base table |
S |
system_table |
SQL Table-valued function |
TF |
sql_table_valued_function |
Table (user-defined type) |
U |
user_table |
UNIQUE constraint |
UQ |
unique_constraint |
View |
V |
view |
So the objects contained in the query database can query the sys.objects view directly, for example: querying all views in the database
1 SELECT * from WHERE = ' U '
Because the object ID is unique within the corresponding database, you can join other tables or views at query time, such as querying all stored procedures and their contents in the database
1 SELECT from sys.objects O 2 JOIN on M.object_id= O.object_id3WHERE='P '
2. Other query methods
The stored procedure has a separate system view sys.procedures, which is essentially equivalent to sys.objects WHERE type = ' P '
You can also perform system SP query stored procedures: EXEC Sp_stored_procedures, different:
Sys.procedures (or sys.objects WHERE type = ' P ') contains only user stored procedures, but execution sp_stored_procedures can query all kinds of stored procedures and all functions except system functions
3.sys.modules View
Returns a row for each system object that contains the SQL language definition module, with the associated SQL module for system objects of type FN, IF, P, PC, TF, and V.
Therefore, for these types of objects, you can join the Sys.objects query object's language definition module, which is its contents.
4.sp_helptext need to be aware of
Sp_helptext is a very high-frequency system stored procedure that allows you to quickly list the contents of a specified name stored procedure or function, with the following disadvantages:
① Formatting Code
② when a line exceeds a certain length, the branch displays
When you modify a stored procedure or function, using sp_helptext is likely to result in a malformed format, which can be a big hassle for the next code reading.
Therefore, it is recommended that sp_helptext be limited to viewing the contents of stored procedures or functions.
In addition, encrypted stored procedures cannot be queried through sp_helptext.
This article is to be supplemented
"Organize" SQL Server queries various database objects (tables, indexes, views, charts, stored procedures, etc.)