• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

Alphabetical List

Table 25-6 contains an alphabetical list of the most important system stored procedures and short descriptions of their parameters. The SQL Server Books Online help contains detailed information and practical examples as well as complete information about the possible parameters. It also contains information about other system stored procedures not listed in Table 25-6.

Table 25-6. System Stored Procedures and Descriptions 
Procedure Name Description and Parameters
sp_addlinkedserver Defines a linked server, which allows access to object linking and embedding database (OLE DB) data sources. Furthermore, stored procedures on the linked server can be executed. Linked servers are covered in detail in Chapter 24, “External Data Sources.”
sp_addlinkedserver [ @server = ] 'server' 
  [ , [ @srvproduct = ] 'product_name' ] 
  [ , [ @provider = ] 'provider_name' ] 
  [ , [ @datasrc = ] 'data_source' ] 
  [ , [ @location = ] 'location' ] 
  [ , [ @provstr = ] 'provider_string' ] 
  [ , [ @catalog = ] 'catalog' ]

sp_addlinkedsrvlogin Maps a user name on a local server to a user name on a linked server. In other words, an assignment is made between a user of the SQL server and a user of the data source on the linked server. Linked servers are covered in detail in Chapter 24, “External Data Sources.”
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 
  [ , [ @useself = ] 'useself' ] 
  [ , [ @locallogin = ] 'locallogin' ] 
  [ , [ @rmtuser = ] 'rmtuser' ] 
  [ , [ @rmtpassword = ] 'rmtpassword' ] 

sp_addlogin Creates a SQL Server login (see Chapter 20, “Security”).
sp_addlogin [ @loginame = ] 'login' 
  [ , [ @passwd = ] 'password' ] 
  [ , [ @defdb = ] 'database' ] 
  [ , [ @deflanguage = ] 'language' ] 
  [ , [ @sid = ] sid ] 
  [ , [ @encryptopt = ] 'encryption_option' ]  

sp_addmessage Creates a new error message in the sysmessages system table.
sp_addmessage [ @msgnum = ] msg_id , 
  [ @severity = ] severity , 
  [ @msgtext = ] 'msg' 
  [ , [ @lang = ] 'language' ] 
  [ , [ @with_log = ] 'with_log' ] 
  [ , [ @replace = ] 'replace' ]

sp_addremotelogin Creates a new user name for a remote server.
sp_addremotelogin [ @remoteserver = ] 'remoteserver' 
  [ , [ @loginame = ] 'login' ] 
  [ , [ @remotename = ] 'remote_name' ]

sp_addrole Adds a new database role in the current database.
sp_addrole [ @rolename = ] 'role' 
  [ , [ @ownername = ] 'owner']

sp_addrolemember Creates a security account for a database role in the currently used database.
sp_addrolemember [ @rolename = ] 'role' , 
  [ @membername = ] 'security_account'

sp_addsrvrolemember Adds a user name to a fixed server role.
sp_addsrvrolemember [ @loginame = ] 'login' 
  , [ @rolename = ] 'role'

sp_addtype Creates a user-defined data type.
sp_addtype [ @typename = ] type, 
  [ @phystype = ] system_data_type 
  [ , [ @nulltype = ] 'null_type' ] 
  [ , [ @owner = ] 'owner_name' ]

sp_addumpdevice Adds a dump device to create a backup for the different parts of a database.
sp_addumpdevice [ @devtype = ] 'device_type' , 
  [ @logicalname = ] 'logical_name' , 
  [ @physicalname = ] 'physical_name' 
  [ , { [ @cntrltype = ] controller_type 
      | [ @devstatus = ] 'device_status' 
    } 
  ]

sp_adduser Adds a user; the procedure was used until Microsoft SQL Server 6.5. sp_grantdbaccess should be used with Microsoft SQL Server 7 and later versions.
sp_adduser [ @loginame = ] 'login' 
  [ , [ @name_in_db = ] 'user' ] 
  [ , [ @grpname = ] 'group' ]

sp_approlepassword Changes the password for an application in the current database.
sp_approlepassword [ @rolename = ] 'role' 
  , [ @newpwd = ] 'password'

sp_attach_db Adds data (.mdf) and log (.ldf) files, which can then be addressed as a database.
sp_attach_db [ @dbname = ] 'dbname' 
  , [ @filename1 = ] 'filename_n' [ ,...16 ]

sp_bindefault Binds a default to a data field or user-defined data type. Use of DEFAULT constraint is preferred.
sp_bindefault [ @defname = ] 'default' , 
  [ @objname = ] 'object_name' 
  [ , [ @futureonly = ] 'futureonly_flag' ]

sp_bindrule Binds a rule to a data field.
sp_bindrule [ @rulename = ] 'rule' , 
  [ @objname = ] 'object_name' 
  [ , [ @futureonly = ] 'futureonly_flag' ] 

sp_catalogs Shows a list of the catalogs (databases) on the specified linked servers.
sp_catalogs [ @server_name = ] 'linked_svr'

sp_change_users_login Changes the relationship between a SQL Server user name and a user of the current database.
sp_change_users_login [ @Action = ] 'action' 
  [ , [ @UserNamePattern = ] 'user' ] 
  [ , [ @LoginName = ] 'login' ]

sp_changedbowner Changes the database owner of the current database.
sp_changedbowner [ @loginame = ] 'login' 
  [ , [ @map = ] remap_alias_flag ]

sp_column_privileges Shows the privileges for columns of a table in the currently used database.
sp_column_privileges [ @table_name = ] 'table_name' 
  [ , [ @table_owner = ] 'table_owner' ] 
  [ , [ @table_qualifier = ] 'table_qualifier' ] 
  [ , [ @column_name = ] 'column' ]

sp_columns Shows column information for tables and views of the current database.
sp_columns [ @table_name = ] object 
  [ , [ @table_owner = ] owner ] 
  [ , [ @table_qualifier = ] qualifier ] 
  [ , [ @column_name = ] column ] 
  [ , [ @ODBCVer = ] ODBCVer ]

sp_configure Shows or modifies global configuration settings for the current server.
sp_configure [ [ @configname = ] 'name' ] 
  [ , [ @configvalue = ] 'value' ]

sp_databases Returns a list of the databases on the server.
sp_databases

sp_datatype_info Returns a list of all data types (without an argument). You can also specify a data type as an argument.
sp_datatype_info [ [ @data_type = ] data_type ] 
  [ , [ @ODBCVer = ] odbc_version ]

sp_dbcmptlevel Determines the SQL Server version with which the specified database has to be compatible.
sp_dbcmptlevel [ [ @dbname = ] name ] 
  [ , [ @new_cmptlevel = ] version ]

sp_dbfixedrolepermission Returns the permissions for all fixed database roles or of a selected role.
sp_dbfixedrolepermission [ [ @rolename = ] 'role' ]

sp_dboption Lists or modifies all database options .
sp_dboption [ [ @dbname = ] 'database' ] 
  [ , [ @optname = ] 'option_name' ] 
  [ , [ @optvalue = ] 'value' ]

sp_dbremove Removes a database. This command is outdated and sp_detach_db should be used in SQL Server 7 and later versions.
sp_dbremove [ @dbname = ] 'database' 
  [ , [ @dropdev = ] 'dropdev' ]

sp_defaultdb Assigns a default database to a SQL server login.
sp_defaultdb [ @loginame = ] 'login' , 
  [@defdb =] 'database'

sp_defaultlanguage Changes the default language for a SQL Server login.
sp_defaultlanguage [ @loginame = ] 'login' 
  [ , [ @language = ] 'language' ]

sp_denylogin Prevents connections from being made to a SQL server.
sp_denylogin [ @loginame = ] 'login' 

sp_depends Shows the dependencies of a database object.
sp_depends [ @objname = ] 'object'

sp_detach_db Detaches a database from a server. The .mdf and .ldf files are not deleted by this.
sp_detach_db [ @dbname = ] 'dbname' 
  [ , [ @skipchecks = ] 'skipchecks' ]

sp_dropdevice Detaches a database or backup medium by deleting the relevant entry from the table master.dbo.sysdevices.
sp_dropdevice [ @logicalname = ] 'device' 
  [ , [ @delfile = ] 'delfile' ]

sp_droplinkedsrvlogin Clears the connection between a user name on the local SQL server and the linked server.
sp_droplinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' , 
  [ @locallogin = ] 'locallogin'

sp_droplogin Removes a SQL Server login.
sp_droplogin [ @loginame = ] 'login'

sp_dropmessage Removes an error message from the sysmessages table.
sp_dropmessage [ @msgnum = ] message_number 
  [ , [ @lang = ] 'language' ]

sp_droprole Deletes a role from the current database.
sp_droprole [ @rolename = ] 'role'

sp_droprolemember Deletes a computer or domain security account from a role in the current database.
sp_droprolemember [ @rolename = ] 'role' , 
  [ @membername = ] 'security_account'

sp_dropserver Removes the link to a linked server.
sp_dropserver [ @server = ] 'server' [ , 

[ @droplogins = ] { 'droplogins' | NULL} ]

sp_dropsrvrolemember Removes a SQL Server login or security account from a fixed database role.
sp_dropsrvrolemember [ @loginame = ] 'login' , 

[ @rolename = ] 'role'

sp_droptype Removes a user-defined data type.
sp_droptype [ @typename = ] 'type'

sp_executesql Executes one or more Transact-SQL statements and can contain parameters.
sp_executesql [@stmt =] stmt
[ 
  {, [@params =] N'@parameter_name data_type 
  [,...n]' } 
  {, [@param1 =] 'value1' [,...n] }
]

sp_fkeys Returns information about foreign key relationships.
sp_fkeys [ @pktable_name = ] 'pktable_name' 
  [ , [ @pktable_owner = ] 'pktable_owner' ] 
  [ , [ @pktable_qualifier = ] 'pktable_qualifier' ] 
  { , [ @fktable_name = ] 'fktable_name' } 
  [ , [ @fktable_owner = ] 'fktable_owner' ] 
  [ , [ @fktable_qualifier = ] 'fktable_qualifier' ] 

sp_foreignkeys Returns the foreign keys that point to primary keys in a table on the linked server.
sp_foreignkeys [ @table_server = ] 'table_server' 
  [ , [ @pktab_name = ] 'pktab_name' ] 
  [ , [ @pktab_schema = ] 'pktab_schema' ] 
  [ , [ @pktab_catalog = ] 'pktab_catalog' ] 
  [ , [ @fktab_name = ] 'fktab_name' ] 
  [ , [ @fktab_schema = ] 'fktab_schema' ] 
  [ , [ @fktab_catalog = ] 'fktab_catalog' ]

sp_grantdbaccess Creates a security account on the current database for a SQL Server login or a Windows NT user or group.
sp_grantdbaccess [@loginame =] 'login'
  [, [@name_in_db =] 'name_in_db' [OUTPUT]]

sp_grantlogin Allows a Windows NT user or group account to log in to a SQL server as a Windows NT-authenticated user.
sp_grantlogin [@loginame =] 'login'

sp_help Provides information about database objects with the exception of triggers.
sp_help [ [ @objname = ] name ]

sp_helpconstraint Provides information about a table’s constraints.
sp_helpconstraint [ @objname = ] 'table' 
  [ , [ @nomsg = ] 'no_message' ]

sp_helpdb Provides information on all databases or specified databases.
sp_helpdb [ [ @dbname= ] 'name' ]

sp_helpdbfixedrole Provides information on all database roles or a fixed database role.
sp_helpdbfixedrole [@rolename =] 'role'

sp_helpdevice Provides information on SQL Server database files. This procedure is provided to ensure backward compatibility.
sp_helpdevice [ [ @devname= ] 'name' ]

sp_helpfile Returns the physical names and attributes of the files that are assigned to the current database. You can use this stored procedure to determine the names of files that are to be appended to or detached from the server.
sp_helpfile [ [ @filename = ] 'name' ]

sp_helpfilegroup Returns the names and attributes of file groups that are assigned to the current database.
sp_helpfilegroup [ [ @filegroupname = ] 'name' ]

sp_helpindex Returns information on the indexes in a table or view.
sp_helpindex [ @objname = ] 'name'

sp_helplanguage Returns information on all languages or on a specific alternative language.
sp_helplanguage [ [ @language = ] 'language' ]

sp_helplinkedsrvlogin Provides information on the defined user name assignments for a specific linked server, which are used for distributed queries and remote stored procedures.
sp_helplinkedsrvlogin [[ @rmtsrvname =] 'rmtsrvname' ] 
  [ , [ @locallogin = ] 'locallogin' ]

sp_helplogins Provides information about SQL Server logins and the associated users in the databases.
sp_helplogins [ [ @LoginNamePattern = ] 'login' ]

sp_helpntgroup Returns information about Windows NT groups with accounts in the current database.
sp_helpntgroup [ [ @ntname = ] 'name' ] 

sp_helprole Returns information about the roles in the current database.
sp_helprole [ [ @rolename = ] 'role' ] 

sp_helprolemember Returns information about the members of a role in the current database.
sp_helprolemember [ [ @rolename = ] 'role' ]

sp_helprotect Returns a report with information about user permissions for an object in the current database.
sp_helprotect [ [ @name = ] 'object_statement' ] 
  [ , [ @username = ] 'security_account' ] 
  [ , [ @grantorname = ] 'grantor' ] 
  [ , [ @permissionarea = ] 'type' ]

sp_helpserver Returns information about remote or replication servers.
sp_helpserver [ [ @server = ] 'server' ] 
  [ , [ @optname = ] 'option' ] 
  [ , [ @show_topology = ] 'show_topology' ]

sp_helpsort Displays the sort order and the character set.
sp_helpsort

sp_helpsrvrole Returns a list for all SQL Server roles or for a fixed server role.
sp_helpsrvrole [ [ @srvrolename = ] 'role' ]

sp_helpsrvrolemember Returns information about all members or one member of a fixed server role.
sp_helpsrvrolemember [ [ @srvrolename = ] 'role' ]

sp_helptext Prints the text of a rule, standard, stored procedure, user-defined function, trigger, or view.
sp_helptext [ @objname = ] 'name'

sp_helptrigger Returns the trigger types defined for the specified table.
sp_helptrigger [ @tabname = ] 'table' 
  [ , [ @triggertype = ] 'type' ]

sp_helpuser Returns information about users, NT users, and database roles in the current database.
sp_helpuser [ [ @name_in_db = ] 'security_account' ]

sp_indexes Returns index information for the specified remote table.
sp_indexes [ @table_server = ] 'table_server' 
  [ , [ @table_name = ] 'table_name' ] 
  [ , [ @table_schema = ] 'table_schema' ] 
  [ , [ @table_catalog = ] 'table_db' ] 
  [ , [ @index_name = ] 'index_name' ] 
  [ , [ @is_unique = ] 'is_unique' ]

sp_linkedservers Returns the list of linked servers, which is defined on the local server.
sp_linkedservers 

sp_makewebtask Creates a task that fills a Hypertext Markup Language (HTML) document with data returned by the executed queries.
sp_makewebtask [@outputfile =] 'outputfile', [@query =] 'query'
  [, [@fixedfont =] fixedfont] 
  [, [@bold =] bold] 
  [, [@italic =] italic] 
  [, [@colheaders =] colheaders] 
  [, [@lastupdated =] lastupdated] 
  [, [@HTMLheader =] HTMLheader] 
  [, [@username =] username] 
  [, [@dbname =] dbname] 
  [, [@templatefile =] 'templatefile'] 
  [, [@webpagetitle =] 'webpagetitle'] 
  [, [@resultstitle =] 'resultstitle']
  [
    [, [@URL =] 'URL', [@reftext =] 'reftext'] 
    | [, [@table_urls =] table_urls, [@url_query =] 
    'url_query']
  ]
  [, [@whentype =] whentype] 
  [, [@targetdate =] targetdate]
  [, [@targettime =] targettime] 
  [, [@dayflags =] dayflags]
  [, [@numunits =] numunits] 
  [, [@unittype =] unittype]
  [, [@procname =] procname] 
  [, [@maketask =] maketask]
  [, [@rowcnt =] rowcnt] 
  [, [@tabborder =] tabborder]
  [, [@singlerow =] singlerow] 
  [, [@blobfmt =] blobfmt]
  [, [@nrowsperpage =] n] 
  [, [@datachg =] table_column_list]
  [, [@charset =] characterset]
  [, [@codepage =] codepage]


					  

sp_monitor Displays statistics about SQL Server.
sp_monitor

sp_password Adds or modifies a password for a SQL Server user name.
sp_password [ [ @old = ] 'old_password' , ] 
  [ @new =] 'new_password'  
  [ , [ @loginame = ] 'login' ]

sp_pkeys Returns primary key information for a single table.
sp_pkeys [ @table_name = ] 'name' 
  [ , [ @table_owner = ] 'owner' ] 
  [ , [ @table_qualifier = ] 'qualifier' ]

sp_primarykeys Returns the primary key columns for the specified remote table.
sp_primarykeys [ @table_server = ] 'table_server' 
  [ , [ @table_name = ] 'table_name' ] 
  [ , [ @table_schema = ] 'table_schema' ] 
  [ , [ @table_catalog = ] 'table_catalog' ]

sp_processmail Processes incoming mail queries on the SQL server. The result of the query is returned to the sender by mail.
sp_processmail [ [ @subject = ] 'subject' ] 
  [ , [ @filetype = ] 'filetype' ] 
  [ , [ @separator = ] 'separator' ] 
  [ , [ @set_user = ] 'user' ] 
  [ , [ @dbuse = ] 'dbname' ] 

sp_recompile Causes stored procedures and triggers to be recompiled the next time they are executed.
sp_recompile [@objname =] 'object'

sp_rename Renames an object created by the user in the current database (for example, table, column, user-defined data type).
sp_rename [@objname = ] 'object_name', 
  [@newname =] 'new_name' 
  [ , [ @objtype = ] 'object_type' ]

sp_renamedb Renames a database.
sp_renamedb [ @dbname = ] 'old_name' , 
  [@newname =] 'new_name'

sp_revokedbaccess Removes a security account from the current database.
sp_revokedbaccess [ @name_in_db = ] 'name'

sp_revokelogin Removes the user name entries in SQL Server for Windows NT users or groups, which were created with sp_grantlogin.
sp_revokelogin [ @loginame = ] 'login'

sp_runwebtask Runs a previously defined Web task (sp_makewebtask) and creates the HTML document.
sp_runwebtask [ [ @procname = ] 'procname' ] 
  [ , [ @outputfile = ] 'outputfile'

sp_server_info Returns a list of information about the server.
sp_server_info [[@attribute_id =] 'attribute_id']

sp_srvrolepermission Returns the permissions applicable to a fixed server role.
sp_srvrolepermission [[@srvrolename =] 'role']

sp_statistics Returns a list with all the indexes and statistics for a specific table or indexed view.
sp_statistics [@table_name =] 'table_name'
[,[@table_owner =] 'owner'] 
[,[@table_qualifier =] 'qualifier'] 
[,[@index_name =] 'index_name'] 
[,[@is_unique =] 'is_unique']
[,[@accuracy =] 'accuracy']

sp_stored_procedures Returns a list of the stored procedures in the current environment.
sp_stored_procedures [[@sp_name =] 'name'] 
[,[@sp_owner =] 'owner'] 
[,[@sp_qualifier =] 'qualifier']

sp_table_privileges Returns a list of table privileges.
sp_table_privileges [ @table_name_pattern = ] 'table_name_pattern' 
[, [@table_owner_pattern = ] 'table_owner_pattern' ] 
[, [@table_qualifier = ] 'table_qualifier' ]

sp_tables Returns a list of objects that can be queried in the current environment.
sp_tables [ [ @table_name = ] 'name' ] 
[ , [ @table_owner = ] 'owner' ] 
[ , [ @table_qualifier = ] 'qualifier' ] 
[ , [ @table_type = ] 'type' ]

sp_unbindefault Removes a default from a column or user-defined data type.
sp_unbindefault [@objname =] 'object_name' 
[, [@futureonly =] 'futureonly_flag']

sp_unbindrule Removes the rule from a column or a user-defined data type.
sp_unbindrule [@objname =] 'object_name' 
[, [@futureonly =] 'futureonly_flag'] 

sp_validatelogins Provides information about lost Windows NT users and groups that no longer exist in the Windows NT environment but for which there are still entries in the SQL Server system tables.
sp_validatelogins

sp_validname Checks for valid SQL Server names.
sp_validname [@name =] 'name' 
[, [@raise_error =] raise_error]

sp_who Provides information about the current SQL Server users and processes.
sp_who [[@login_name =] 'login']



PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint