Useful System SPs of SQL Server 2005

sp_add_data_file_recover_suspect_db (Transact-SQL)
Adds a data file to a filegroup when recovery cannot complete on a database due to insufficient space on the file group (error 1105). After the file is added, this stored procedure turns off the suspect setting and completes the recovery of the database. The parameters are the same as those for ALTER DATABASE database_name ADD FILE.
Click this link

sp_addextendedproc (Transact-SQL
Registers the name of a new extended stored procedure to Microsoft SQL Server.
Click this link

sp_addextendedproperty (Transact-SQL)
Adds a new extended property to a database object.
Click this link

sp_add_log_file_recover_suspect_db (Transact-SQL)
Adds a log file to a file group when recovery cannot complete on a database due to insufficient log space (error 9002). After the file is added, sp_add_log_file_recover_suspect_db turns off the suspect setting and completes the recovery of the database. The parameters are the same as those for ALTER DATABASE database_name ADD LOG FILE.
Click this link

sp_addmessage (Transact-SQL)
Stores a new user-defined error message in an instance of the SQL Server Database Engine. Messages stored by using sp_addmessage can be viewed by using the sys.messages catalog view.
Click this link

sp_addtype (Transact-SQL)
Creates an alias data type.
Click this link

sp_addumpdevice (Transact-SQL)
Adds a backup device to an instance of the Microsoft SQL Server 2005 Database Engine.
Click this link

sp_altermessage (Transact-SQL)
Alters the state of user-defined messages in an instance of the SQL Server Database Engine. User-defined messages can be viewed using the sys.messages catalog view.
Click this link

sp_attach_db (Transact-SQL)
Attaches a database to a server.
Click this link

sp_attach_single_file_db (Transact-SQL)
Attaches a database that has only one data file to the current server. sp_attach_single_file_db cannot be used with multiple data files.
Click this link

sp_autostats (Transact-SQL)
Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a specified table or indexed view in the current database.
Click this link

sp_batch_params (Transact-SQL)
Returns a rowset that contains information about the parameters included in a Transact-SQL batch. sp_batch_params only parses the batch specified and returns information about embedded parameter values. It does not execute the batch or modify the execution environment.
Click this link

sp_bindefault (Transact-SQL)
Binds a default to a column or to an alias data type.
Click this link

sp_bindrule (Transact-SQL)
Binds a rule to a column or to an alias data type.
Click this link

sp_bindsession (Transact-SQL)
Binds or unbinds a session to other sessions in the same instance of the SQL Server Database Engine. Binding sessions allows two or more sessions to participate in the same transaction and share locks until a ROLLBACK TRANSACTION or COMMIT TRANSACTION is issued.
Click this link

sp_certify_removable (Transact-SQL)
Verifies that a database is correctly configured for distribution on removable media and reports any problems to the user.
Click this link

sp_configure (Transact-SQL)
Displays or changes global configuration settings for the current server.
Click this link

sp_control_plan_guide (Transact-SQL)
Drops, enables, or disables a plan guide.
Click this link

sp_create_plan_guide (Transact-SQL)
Creates a plan guide for associating query hints with queries in a database.
Click this link

sp_create_removable (Transact-SQL)
Creates a removable media database. Creates three or more files (one for the system catalog tables, one for the transaction log, and one or more for the data tables) and places the database on those files.
Click this link

sp_createstats (Transact-SQL)
Creates single-column statistics for all eligible columns for all user tables and internal tables in the current database. The new statistic has the same name as the column where it is created.
Click this link

sp_datatype_info (Transact-SQL)
Returns information about the data types supported by the current environment.
Click this link

sp_db_vardecimal_storage_format (Transact-SQL)
Returns the current vardecimal storage format state of a database, or enables or disables a database for vardecimal storage format. Requires SQL Server 2005 Service Pack 2 or later versions. Vardecimal storage format is available only in SQL Server 2005 Enterprise, Developer, and Evaluation editions.
Click this link

sp_dbcmptlevel (Transact-SQL)
Sets certain database behaviors to be compatible with the specified version of SQL Server.
Click this link

sp_dbmmonitoraddmonitoring (Transact-SQL)
Creates a database mirroring monitor job that periodically updates the mirroring status for every mirrored database on the server instance.
click this link

sp_dbmmonitorchangealert (Transact-SQL)
Adds or changes warning threshold for a specified mirroring performance metric.
Click this link

sp_dbmmonitorchangemonitoring (Transact-SQL)
Changes the value of a database mirroring monitoring parameter.
Click this link

sp_dbmmonitordropalert (Transact-SQL)
Drops the warning for a specified performance metric, by setting the threshold to NULL.
Click this link

sp_dbmmonitordropmonitoring (Transact-SQL)
Stops and deletes the mirroring monitor job for all the databases on the server instance.
Click this link

sp_dbmmonitorhelpalert (Transact-SQL)
Returns information about warning thresholds on one or all of several key database mirroring monitor performance metrics.
Click this link

sp_dbmmonitorhelpmonitoring (Transact-SQL)
Returns the current update period.
Click this link

sp_dbmmonitorresults (Transact-SQL)
Returns status rows for a monitored database from the status table in which database mirroring monitoring history is stored and allows you to choose whether the procedure obtains the latest status beforehand.
Click this link

sp_dbmmonitorupdate (Transact-SQL)
Updates the database mirroring monitor status table by inserting a new table row for each mirrored database, and truncates rows older than the current retention period. The default retention period is 7 days (168 hours). When updating the table, sp_dbmmonitorupdate evaluates the performance metrics.
Click this link

sp_dboption (Transact-SQL)
Displays or changes database options. Do not use sp_dboption to modify options on either the master database or the tempdb database.
Click this link

sp_dbremove (Transact-SQL)
Removes a database and all files associated with that database.
Click this link

sp_delete_backuphistory (Transact-SQL)
Reduces the size of the backup and restore history tables by deleting the entries for backup sets older than the specified date. Additional rows are added to the backup and restore history tables after each backup or restore operation is performed; therefore, we recommend that you periodically execute sp_delete_backuphistory.
Click this link

sp_delete_database_backuphistory (Transact-SQL)
Deletes information about the specified database from the backup and restore history tables.
Click this link

sp_depends (Transact-SQL)
Displays information about database object dependencies, such as: the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure. References to objects outside the current database are not reported.
Click this link

sp_detach_db (Transact-SQL)
Detaches a database that is currently not in use from a server instance and, optionally, runs UPDATE STATISTICS on all tables before detaching.
Click this link

sp_dropdevice (Transact-SQL)
Drops a database device or backup device from an instance of the SQL Server 2005 Database Engine, deleting the entry from master.dbo.sysdevices.
Click this link

sp_dropextendedproc (Transact-SQL)
Drops an extended stored procedure.
Click this link

sp_dropextendedproperty (Transact-SQL)
Drops an existing extended property.
Click this link

sp_dropmessage (Transact-SQL)
Drops a specified user-defined error message from an instance of the SQL Server Database Engine. User-defined messages can be viewed using the sys.messages catalog view.
Click this link

sp_droptype (Transact-SQL)
Deletes an alias data type from systypes.
Click this link

sp_estimated_rowsize_reduction_for_vardecimal (Transact-SQL)
Estimates the reduction in the average size of rows if enable vardecimal storage format is enabled on a table. Use this number to estimate the overall reduction in the size of the table. Because the statistical sampling is used to compute the average reduction in the rowsize, regard it as an estimate only. In rare cases, rowsize may increase after you enable the vardecimal storage format. Vardecimal storage format is available only in SQL Server 2005 Enterprise, Developer, and Evaluation editions.
Click this link

sp_executesql (Transact-SQL)
Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.
Click this link

sp_get_query_template
Returns the parameterized form of a query. The results returned mimic the parameterized form of a query that results from using forced parameterization. sp_get_query_template is used primarily when you create TEMPLATE plan guides.
Click this link

sp_getapplock (Transact-SQL)
Places a lock on an application resource.
Click this link

sp_getbindtoken (Transact-SQL)
Returns a unique identifier for the transaction. This unique identifier is a string used to bind sessions using sp_bindsession.
Click this link

sp_help (Transact-SQL)
Reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type supplied by SQL Server 2005.
Click this link

sp_helpconstraint (Transact-SQL)
Returns a list of all constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines the constraint (for DEFAULT and CHECK constraints only).
Click this link

sp_helpdb (Transact-SQL)
Reports information about a specified database or all databases.
Click this link

sp_helpdevice (Transact-SQL)
Reports information about Microsoft® SQL Server™ backup devices.
Click this link

sp_helpextendedproc (Transact-SQL)
Reports the currently defined extended stored procedures and the name of the dynamic-link library (DLL) to which the procedure (function) belongs.
Click this link

sp_helpfile (Transact-SQL)
Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server.
Click this link

sp_helpfilegroup (Transact-SQL)
Returns the names and attributes of filegroups associated with the current database.
Click this link

sp_helpgroup (Transact-SQL)
Reports information about a role, or all roles, in the current database.
Click this link

sp_helpindex (Transact-SQL)
Reports information about the indexes on a table or view.
Click this link

sp_helplanguage (Transact-SQL)
Reports information about a particular alternative language or about all languages.
Click this link

sp_helpserver (Transact-SQL)
Reports information about a particular remote or replication server, or about all servers of both types. Provides the server name, the network name of the server, the replication status of the server, the identification number of the server, and the collation name. Also provides time-out values for connecting to, or queries against, linked servers.
Click this link

sp_helpsort (Transact-SQL)
Displays the sort order and character set for the instance of SQL Server 2005.
Click this link

sp_helpstats (Transact-SQL)
Returns statistics information about columns and indexes on the specified table.
Click this link

sp_helptext (Transact-SQL)
Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.
Click this link

sp_helptrigger (Transact-SQL)
Returns the type or types of DML triggers defined on the specified table for the current database. sp_helptrigger cannot be used with DDL triggers. Query the sys.triggers (Transact-SQL) catalog view instead.
Click this link

sp_indexoption (Transact-SQL)
Sets locking option values for user-defined clustered and nonclustered indexes or tables with no clustered index.
The SQL Server 2005 Database Engine automatically makes choices of page-, row-, or table-level locking. You do not have to set these options manually. sp_indexoption is provided for expert users who know with certainty that a particular type of lock is always appropriate.
Click this link

sp_invalidate_textptr (Transact-SQL)
Invalidates the specified in-row text pointer, or all in-row text pointers, in the transaction. sp_invalidate_textptr can be used only on in-row text pointers. These pointers are from tables that have the text in row option enabled.
Click this link

sp_lock (Transact-SQL)
Reports information about locks.
Click this link

sp_monitor (Transact-SQL)
Displays statistics about Microsoft SQL Server.
Click this link

sp_procoption (Transact-SQL)
Sets stored procedure for autoexecution. A stored procedure that is set to autoexecution runs every time an instance of SQL Server is started.
Click this link

sp_recompile (Transact-SQL)
Causes stored procedures and triggers to be recompiled the next time they are run.
Click this link

sp_refreshsqlmodule (Transact-SQL)
Updates the metadata for the specified nonschema-bound stored procedure, user-defined function, or view. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.
Click this link

sp_refreshview (Transact-SQL)
Updates the metadata for the specified non-schemabound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.
Click this link

sp_releaseapplock (Transact-SQL)
Releases a lock on an application resource.
Click this link

sp_rename (Transact-SQL)
Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.
Click this link

sp_renamedb (Transact-SQL)
Changes the name of a database.
Click this link

sp_resetstatus (Transact-SQL)
Resets the status of a suspect database.
Click this link

sp_setnetname (Transact-SQL)
Sets the network names in sys.servers to their actual network computer names for remote instances of SQL Server 2005. This procedure can be used to enable execution of remote stored procedure calls to computers that have network names containing SQL Server identifiers that are not valid.
Click this link

sp_settriggerorder (Transact-SQL)
Specifies the AFTER triggers that are fired first or last. The AFTER triggers that are fired between the first and last triggers are executed in undefined order.
Click this link

sp_spaceused (Transact-SQL)
Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or SQL Server 2005 Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.
Click this link

sp_tableoption (Transact-SQL)
Sets option values for user-defined tables. sp_tableoption can be used to control the in-row behavior of tables with varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, or image columns.
Click this link

sp_unbindefault (Transact-SQL)
Unbinds, or removes, a default from a column or from an alias data type in the current database.
Click this link

sp_unbindrule (Transact-SQL)
Unbinds a rule from a column or an alias data type in the current database.
Click this link

sp_updateextendedproperty (Transact-SQL)
Updates the value of an existing extended property.
Click this link

sp_updatestats (Transact-SQL)
Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.
Click this link

sp_validname (Transact-SQL)
Checks for valid SQL Server 2005 identifier names. All nonbinary and nonzero data, including Unicode data that can be stored by using the nchar, nvarchar, or ntext data types, are accepted as valid characters for identifier names.
Click this link

sp_who (Transact-SQL)
Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.
Click this link

Advertisements
This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s