"Organize" SQL Server queries various database objects (tables, indexes, views, charts, stored procedures, etc.)

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.)