SQL Server System Stored Procedure Analysis 2-Database Engine Stored Procedure

 

1. sp_configure

Definition: displays or changes the global configuration settings of the current server.

Main result set:Config_value, Configuration value of configuration options; Run_valueRunning value of configuration options;If the two are inconsistent, the configuration is successful but does not take effect.

Note: It is useful. Use sp_configure to display or change server-level settings. To change the database level settings, useAlter Database. To change the settings that only affect the current user session, useSetStatement.

RelatedArticle: SQL ServerMemory usage Solution

 

2. sp_help

Definition: Report database objects (SYS. sysobjectsCompatible with all objects listed in the view), user-defined data type, or information of a certain data type

Note: The returned information is rich and the stored procedure is more practical.

 

3. sp_helpdb

Definition: reports information about a specified database or all databases.

Note: It can return all database information or detailed information of a single database, which is more practical and can be used to replaceSp_databases

 

4. sp_helptext

Definition: displays the definition, default value, and unencrypted user-defined rules.Transact-SQLStored Procedures, user-definedTransact-SQLFunctions, triggers, calculation columns,CheckConstraints, views, or system objects (such as system stored procedures ).

Note: It is very useful, especially for viewing the definition of a stored procedure.

 

5. sp_procoption

Definition: sets the automatically executed stored procedure. The stored procedure is set to automatically executed at each startup.SQL ServerRun on the instance.

Note: although the application scenario is narrow, it is quite useful and can solve the problem in its application environment.

 

6. sp_refreshview

Definition: used to update the metadata of a view not bound to the schema. Due to changes to the basic object on which the view depends, the persistent metadata of the view will expire.

Note: When the related tables or auto-disconnections are used to update the view, use this stored procedure to update the related view.

 

7. sp_rename

Definition: change the name of the object created by the user in the current database. This object can be a table, index, column, alias data type orMicrosoft. NET FrameworkPublic Language Runtime(CLR)User-Defined type.

8. sp_spaceused

Definition: displays the number of rows, reserved disk space, tables in the current database, index views, orService BrokerThe disk space used by the queue, or the disk space reserved and used by the entire database.

Note: You can view tables or databases.SpaceUsage

9. sp_who

Note: a very practical stored procedure is mainly used to view the current dB connections, the same stored procedures or Commands include: sp_who , sp_who active , sp_who2 and sp_who2 active , Search learning

Related Articles: SQL Server 2008 r2Solution to excessive connections