SQL drip 17-using Database engine stored procedures, System view queries, DBA,BI Developer Essential Basics

Original: SQL Drip 17-using the database engine stored procedures, System view queries, DBA,BI Developer Prerequisite Basics

　　

In the development process will encounter the need to figure out when the database was built, how many tables in this database, what is the length of the stored procedure, and so on, today, the process of working with some of the database engine stored procedures, system views and so on for the time being. The following knowledge is mostly self-summarizing, with some reference to MSDN.

　　

Sp_help

Sometimes you want to find out about database objects as soon as possible, this stored procedure is very useful. Use it to query information about all objects in the entire database. Directly run sp_help results such as 1, above is the database object, contains the system custom tables, views and so on, the following is the custom data type

Figure 1

If I only want to find the length of a data type, precision, etc. information can be run sp_help datatypename, such as 2

Figure 2

If I want to find out about a table, I can run sp_help tablename, like 3.

Figure 3

Run the results of the first isolated table basic information, the second to find out all the columns, the third to identify the primary key information, the fourth table to isolate the identity of the column, fifth to return the filegroup information.

If I want to find out about a stored procedure, you can use sp_help procname as above, like 4

Figure 4

It is important to note that sometimes it is necessary to enclose database objects in single quotes. The first table identifies information such as the name of a stored procedure, and the second identifies 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, 5

Figure 5

But this is a bit verbose, if there are a lot of stored procedures we can not quickly click to select which one we want to check. If you use sp_helptext procname you can quickly find this information, 6

Figure 6

, the default is to use the grid to display text, or you can use text formatting, as long as you click Result to text to display the text format. If you still feel this way, the keyword has no color, you can select all the text to copy to the SQL Text editor. You can also use sp_helptext to isolate the text content of objects such as views, functions, and custom computed columns in a database.

　　

sp_helpdb

Generally we get the customer's database server, we prefer to find out how many databases on this server, respectively, the user created information, and so on, you may directly open the database with the mouse click to view, but if there are a lot of database is not so convenient, this time directly run sp_ Helpdb is easy to find out. such as 7.

Figure 7

I have queried all the database information in my database software. If I want to know more about a particular database, you can use sp_helpdb dbname query, 8.

Figure 8

The first table identifies the basic information for this database, and the second table queries the database file information for the database. There are many other database engine stored procedures, such as:

Sp_helpconstraint can detect the constraint information encountered in the current database.

Sp_helpextendedproc can query all extended stored procedures, as well as the name of the DLL that belongs to, as mentioned in the previous essay: SQL bit 15-called in SQL Server 2008 in C # programs.

Sp_helpfile and sp_helpdb dbname the same effect, querying the database file information of the current database.

Sp_helpfilegroup returns information about the filegroup associated with the database file of the current database.

Sp_helpindex TableName Returns information about an index in a table or view.

Sp_helplanguage returns the language information that is installed in the database.

Sp_helpserver displays information for all servers that are currently connected.

Sp_helpsort returns the default collation for the server.

　　

Sys.objects

database development and application development are somewhat different, there is no management tool like CC,VSS,SVN to manage the code, because all the code is stored in the database and not tracked. Let's say we wait for the Database Designer to import the data after the table or stored procedure is built, but 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 the user tables, views, and stored procedures in the database and sorts them by time, so that you can see others recently added and modified those database objects. Sys.objects does not contain triggers, and triggers are in sys.triggers. There are similar sys.tables,sys.views and so on.

　　

Information_schema

If we want to quickly query whether the database contains an address such as the column of the word, and find it in that database, the table, its data type information, and so on, it is useful to use INFORMATION_SCHEMA This schema information, such as 9.

Figure 9

There is a lot of information about the same architecture in information_schema such as:

Information_schema.tables returns the table information,

Information_schema.check_constraints returns the check constraint information,

Information_schema.views returns the View information,

Information_schema.routines returns stored procedure information, and many more are not listed here.

　　

In short, the SQL Server database provides a wealth of object statistics for our use, which is just a part of what I used to do here.

SQL drip 17-using Database engine stored procedures, System view queries, DBA,BI Developer Essential Basics