SQL 17-basic knowledge required by database engine stored procedures, System View queries, DBAs, AND Bi developers

　　

During the development process, you may need to find out when the database is created, how many tables are in the database, how long the Stored Procedure looks, and so on, today, I will summarize some of the Database Engine stored procedures and system views that are frequently used in my work for use from time to time. Most of the Knowledge below is summarized by myself. I have some reference to msdn.

　　

Sp_help

Sometimes it is useful to find out information about database objects as soon as possible. It can be used to query information about all objects in the entire database. Run sp_help directly. The result is 1. The above is a database object, including a custom table and view. The following is a custom data type.

Figure 1

If I only want to find the length and precision of a certain data type, run sp_help datatypename, such as 2

Figure 2

To find information about a table, run sp_help tablename, for example, 3.

Figure 3

The first basic information of the running result is the basic information of the table, the second is all the columns, the third is the primary key information, the fourth is the column identification, and the fifth is the file group information.

If I want to find information about a stored procedure, I can directly use sp_help procname, as shown in figure 4.

Figure 4

Note that you sometimes need to use single quotes to include database objects. The first table finds information such as the name of the stored procedure, and the second table finds the parameter information.

　　

Sp_helptext

Sometimes we need to find out what the Stored Procedure looks like, although you can click script stored procedure as, create to, new query editor window to find, 5

Figure 5

However, this is a bit cool. If there are a lot of stored procedures, we cannot quickly click which one we want to check. If you use sp_helptext procname, you can quickly find the information

Figure 6

By default, grid is used to display text. You can also use the text format. You only need to click result to text to display the text format. If you still feel this uncomfortable, and the keywords do not have color, you can select all the text to copy to the SQL text editor. Similarly, you can use sp_helptext to find out the text content of objects such as views, functions, and custom computing columns in the database.

　　

Sp_helpdb

Generally, when we obtain the customer's database server, we first need to figure out the number of databases on the server, such as those created by users, you may open the database directly and click it to view it. However, if there are many databases, it is not that convenient. At this time, it is easy to run sp_helpdb directly. For example, 7.

Figure 7

I found all the database information in my database software. If you want to know the details of a database, you can use sp_helpdb dbname to query, 8.

Figure 8

The first table finds the basic information of the database, and the second table queries the database file information of the database. There are many other database engine stored procedures, such:

Sp_helpconstraint can be used to identify the constraints in the current database.

Sp_helpextendedproc can query all the Extended Stored Procedures and the name of the DLL to which they belong, as mentioned in the previous article: SQL bit by bit 15-Calling C # programs in SQL Server 2008.

The effects of sp_helpfile and sp_helpdb dbname are similar. query the database file information of the current database.

Sp_helpfilegroup: returns information about the file groups associated with the database files of the current database.

Sp_helpindex tablename returns the index information in the table or view.

Sp_helplanguage returns the language information installed in the database.

Sp_helpserver displays information about all servers currently connected.

Sp_helpsort returns the default sorting rule of the server.

　　

SYS. Objects

Database development and application development are somewhat different. There is no management tool like CC, VSS, and SVN to manage code, because all code is stored in the database and should not be tracked. Suppose we want to wait for the Database Designer to import the data after creating a table or stored procedure, but we will not see the movement. In this case, we can look at all the custom objects in the database and sort them by the last update time. Run the following statement: Select * From SYS. objects where type in ('U', 'V', 'P') order by modify_date

This statement identifies all user tables, views, stored procedures, and sorting by time in the database, so that you can see that others have recently added and modified those database objects. SYS. Objects does not contain triggers. The triggers are in SYS. triggers. Similarly, there are SYS. Tables, SYS. Views, and so on.

　　

Information_schema

If we want to quickly query whether the database contains columns with the words "Address" and find out the database, the table, and its data type information, in this case, using information_schema is useful, for example, 9.

Figure 9

In the same information_schema architecture, there is still a lot of information such:

Information_schema.tables returns table information,

Information_schema.check_constraints returns the check constraint information,

Information_schema.views return view information,

Information_schema.routines returns stored procedure information, which is not listed here.

　　

In short, the SQL Server database provides a wealth of object statistics for our use. The above is only a part of what I often use.