Common system stored procedures

The name of the system stored procedure for SQL Server begins with ' sp_ ' and resides in the resource database, where the system administrator has permission to use the stored procedures and can run system stored procedures in any database, but the results of the execution are reflected in the current database.

1.sp_database: List all database information on the server, including database name and size

Usage:exec sp_databases

2.SP_HELPDB: report information about a specified database or all databases

Usage:exec sp_helpdb

3.sp_renamedb: Change the name of the database

Usage: exec sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name'

4.sp_tables: Returns a table or attempted information that can be queried in the current environment

Usage:

  sp_tables  [ @table_name   = ]  '   name   ' ] [,  [ @table_owner   = ]  '   owner   ' ] [,  [ @table_qualifier   = ] 
   
     '  
     qualifier  
     ' ] [
    ,  [
     @table_type  
     =< /strong>]  "  type  "  "[,  [ @fUsePattern =  ]  '   fusepattern   ' ]; 
 
 5.sp_columns: Returns column information for the specified table or view that can be queried in the current environment, including the data type and length of the column 
 usage: 
   

sp_columns @table_name = Object  , @table_owner =  ]      ,@table_qualifier=]      ,@column_ Name=]      ,@ODBCVer=]
6.sp_help: Reports information about database objects (all objects listed in thesys.sysobjects compatibility View), user-defined data types, or data types.

Usage: sp_help @objname = ' name ' ]

Parameters

[ @objname =] ' name '

The name of an object of any user-defined data type in the sysobjects type or systypes table. The data type of name is nvarchar (776)and the default value is NULL. The database name cannot be accepted.

7.sp_stored_procedures: Displays a list of stored procedures

Sp_stored_procedures'name] ' Schema' ] [qualifier] ['fusepattern]

Parameters

[ @sp_name = ] ' name '

The name of the procedure used to return directory information. The data type of name is nvarchar (390)and the default value is NULL. Wildcard pattern matching is supported.

[ @sp_owner = ] ' Schema '

The name of the schema to which the procedure belongs. The schema has a data type of nvarchar (384), and the default value is NULL. Wildcard pattern matching is supported. If owneris not specified, the default procedure visibility rule for the underlying DBMS is followed.

In SQL Server, this procedure is returned if the current schema contains a procedure with the specified name. If unqualified stored procedures are specified, the database engine searches for this procedure in the following order:

• The sys schema of the current database.
• The default schema for the caller (when using batch or dynamic SQL execution), or if the unqualified procedure name appears in the body of another procedure definition, then search for the schema that contains the procedure. For more information about the default schema, see User schema separation.
• The dbo schema in the current database.

[ @qualifier = ] ' Qualifier '

The name of the procedure qualifier. The qualifier data type is sysnameand the default value is NULL. Multiple DBMS products support the three-part naming method for tables (qualifier. Schema . name). In SQL Server,qualifier represents the database name. In some products, it represents the server name of the database environment in which the table resides.

[ @fUsePattern = ] ' Fusepattern '

8.sp_password: Add or modify password for login account

sp_password @old = ' Old_password ' , ] @new = ' New_password ' } , @loginame = ' Login ' ]

Parameters

[ @old = ] ' Old_password '

Old password. The old_password data type is sysnameand the default value is NULL.

[ @new = ] ' New_password '

New password. The new_password data type is sysnameand has no default value. If you do not use named parameters, you must specify old_password.

[ @loginame = ] ' Login '

The login name that is affected by the password change. The data type of login is sysnameand the default value is NULL. Login must already exist and can only be specified by members of the sysadmin or securityadmin fixed server role.

Common system stored procedures