Undocumented Procedures

for RuBoard

By my count, there are more than 100 undocumented stored procedures, not counting undocumented replication stored procedures. I've listed many of them in the section that follows . I haven't included every last one of them for several reasons:

1. There are simply too many to cover in any sort of depth. This is why I've intentionally left out the undocumented routines relating to replication. Replication is a world unto itself.

2. Some undocumented routines are so fragile and add so little value to the Transact-SQL command set that they are best left undocumented.

3. Some of the undocumented routines behave so erratically or are so dependent on code external to SQL Server (e.g., in Enterprise Manager or SQL-DMO) that they are either unusable or of little value to the Transact-SQL developer. The idea here is to provide complete coverage without getting carried away.

Each of the following procedures is not documented in the Books Online, but many of them provide useful functionality. You'll have to judge whether their functionality is worth the associated risk.

sp_checknames [@mode]

Checks key system tables for non-ASCII names .

 sp_checknames @mode='silent' 

sp_delete_backuphistory @oldest_date

Clears system backup history prior to a given date.

 msdb..sp_delete_backuphistory @oldest_date datetime 

sp_enumerrorlogs

Enumerates the current server error log files.

 master..sp_enumerrorlogs 

(Results abridged)

 Archive #   Date                Log File Size (Byte) ----------- ------------------- -------------------- 6           06/28/2000  23:13   3139 5           06/29/2000  11:19   3602 4           06/29/2000  11:35   3486 3           06/29/2000  22:55   15998 2           06/29/2000  23:10   3349 1           07/01/2000  12:49   120082 0           07/01/2000  12:51   3532 

sp_enumoledbdatasources

Enumerates the OLEDB data providers visible to the server.

 sp_enumoledbdatasources 

sp_fixindex @dbname, @tabname, @indid

Allows indexes on system tables to be dropped/recreated.

 USE northwind EXEC sp_dboption 'northwind','single',true EXEC sp_fixindex 'northwind', 'sysobjects', 2 EXEC sp_dboption 'northwind','single',false 

sp_gettypestring @tabid, @colid, @typestring output

Renders a textual description of a column's data type.

 declare @tabid int, @typestr varchar(30) SET @tabid=OBJECT_ID('authors') EXEC sp_gettypestring @tabid, 1, @typestr OUT SELECT @typestr 

(Results)

 ------------------------------ varchar(11) 

sp_MS_marksystemobject @objname

Sets an object's system bit (0xC0000000). Several functions and DBCC command verbs do not work properly unless executed from a system object. Setting this bit will cause the IsMSShipped object property to return 1.

 sp_Ms_marksystemobject 'sp_dir' 

sp_MS_upd_sysobj_category @pSeqMode integer

Enables/disables a special system mode wherein newly created objects are automatically system objects. Setting @pSeqMode to 1 enables this mode; setting it to 2 disables it. Among other things, sp_MS_upd_sysobj_category allows the creation of user -defined INFORMATION_SCHEMA views. See Chapter 9 for more information.

 sp_MS_upd_sysobj_category 1 

Sp_MSaddguidcol @source_owner, @source_table

Adds a ROWGUIDCOL column to a table. Also marks the table for replication (use EXEC sp_MSunmarkreplinfo to reverse this).

 sp_MSaddguidcolumn dbo,testguid 

sp_MSaddguidindex @source_owner, @source_table

Creates an index on a table's ROWGUIDCOL column.

 sp_MSaddguidindex dbo,testuid 

sp_MSaddlogin_implicit_ntlogin @ loginname

Adds a SQL Server login that corresponds to an existing NT login.

 sp_MSaddlogin_implicit_ntlogin 'GoofyTingler' 

sp_MSadduser_implicit_ntlogin @ntname

Adds a database user that corresponds to an existing NT login.

 sp_MSadduser_implicit_ntlogin 'GoofyTingler' 

sp_MScheck_uid_owns_anything @uid

Returns 1 when a user owns any objects in the current database.

 DECLARE @res int, @uid int SELECT @uid=USER_ID() EXEC @res=sp_MScheck_uid_owns_anything @uid SELECT @res 

(Results)

 Server: Msg 15183, Level 16, State 1, Procedure sp_MScheck_uid_owns_anything, Line 17 The user owns objects in the database and cannot be dropped. Name type ------------------------------------------------- ---- LastCustNo   U ----------- 1 

sp_MSdbuseraccess @mode='perm''db', @qual=db name mask

Returns a list of databases a user can access and a bitmap representing the access in each.

 sp_MSdbuseraccess @mode='db' name          version crdate                   owner ------------- ------- ------------------------ -------------------- distribution  539     2000-11-28 20:46:14.293  LEX\TALIONIS master        539     2000-08-06 01:29:12.250  sa model         539     2000-08-06 01:40:52.437  sa msdb          539     2000-08-06 01:40:56.810  sa Northwind     539     2000-08-06 01:41:00.310  sa pubs          539     2000-05-06 14:34:09.720  LEX\TALIONIS rentman       NULL    2000-06-30 16:32:11.813  LEX\TALIONIS tempdb        539     2000-07-01 12:51:55.590  sa 

sp_MSdbuserpriv @mode='perm''serv''ver''role'

Returns a bitmap representing user privileges.

 sp_MSdbuserpriv @mode='role' 

(Results)

 ----------- 73855 

sp_MSdependencies @objname, @objtype, @flags int, @objlist

Shows object dependencies.

 sp_MSdependencies @objname = 'titleauthor' 

(Results abridged)

 oType       oObjName    oOwner  oSequence ----------- ----------- ------- --------- 8           authors     dbo     1 8           publishers  dbo     1 8           titles      dbo     2 

sp_MSdrop_object [@object_id] [,@object_name] [,@object_owner]

Generically drops a table, view, trigger, or procedure.

 sp_MSdrop_object @object_name='authors2' 

sp_MSexists_file @full_path, @filename

Checks for the existence of an operating system file (version 7.0 only).

 DECLARE @res int EXEC @res=sp_MSexists_file 'd:\readme.txt', 'readme.txt' 

sp_MSforeachdb @command1 @replacechar = '?' [,@command2] [,@command3] [,@precommand] [,@postcommand]

Executes up to three commands for every database on the system. @replacechar will be replaced with the name of each database. @precommand and @postcommand can be used to direct commands to a single result set.

 EXEC sp_MSforeachdb 'DBCC CHECKDB(?)' EXEC sp_MSforeachdb @command1='PRINT ''Listing ?''', @command2='USE ? SELECT DB_NAME()' 

(Results abridged)

 DBCC results for 'Northwind'. DBCC results for 'sysobjects'. There are 232 rows in 5 pages for object 'sysobjects'. DBCC results for 'sysindexes'. There are 162 rows in 7 pages for object 'sysindexes'. DBCC results for 'syscolumns'. There are 1056 rows in 23 pages for object 'syscolumns'. DBCC results for 'systypes'. There are 26 rows in 1 pages for object 'systypes'. DBCC results for 'syscomments'. There are 232 rows in 25 pages for object 'syscomments'. DBCC results for 'sysfiles1'. There are 2 rows in 1 pages for object 'sysfiles1'. DBCC results for 'syspermissions'. There are 72 rows in 1 pages for object 'syspermissions'. DBCC results for 'sysusers'. There are 14 rows in 1 pages for object 'sysusers'. DBCC results for 'sysproperties'. There are 0 rows in 0 pages for object 'sysproperties'. DBCC results for 'sysdepends'. There are 760 rows in 4 pages for object 'sysdepends'. DBCC results for 'sysreferences'. There are 14 rows in 1 pages for object 'sysreferences'. DBCC results for 'sysfulltextcatalogs'. There are 0 rows in 0 pages for object 'sysfulltextcatalogs'. DBCC results for 'sysfulltextnotify'. There are 0 rows in 0 pages for object 'sysfulltextnotify'. DBCC results for 'sysfilegroups'. There are 1 rows in 1 pages for object 'sysfilegroups'. DBCC results for 'Orders'. There are 830 rows in 26 pages for object 'Orders'. DBCC results for 'pubs'. DBCC results for 'sysobjects'. There are 108 rows in 3 pages for object 'sysobjects'. DBCC results for 'sysindexes'. There are 54 rows in 3 pages for object 'sysindexes'. DBCC results for 'syscolumns'. There are 440 rows in 5 pages for object 'syscolumns'. DBCC results for 'systypes'. There are 29 rows in 1 pages for object 'systypes'. DBCC results for 'syscomments'. There are 149 rows in 11 pages for object 'syscomments'. DBCC results for 'sysfiles1'. There are 2 rows in 1 pages for object 'sysfiles1'. DBCC results for 'syspermissions'. There are 69 rows in 1 pages for object 'syspermissions'. DBCC results for 'sysusers'. There are 13 rows in 1 pages for object 'sysusers'. DBCC results for 'sysproperties'. There are 0 rows in 0 pages for object 'sysproperties'. DBCC results for 'sysdepends'. There are 354 rows in 2 pages for object 'sysdepends'. DBCC results for 'sysreferences'. There are 10 rows in 1 pages for object 'sysreferences'. DBCC results for 'sysfulltextcatalogs'. There are 0 rows in 0 pages for object 'sysfulltextcatalogs'. DBCC results for 'sysfulltextnotify'. There are 0 rows in 0 pages for object 'sysfulltextnotify'. DBCC results for 'sysfilegroups'. There are 1 rows in 1 pages for object 'sysfilegroups'. DBCC results for 'titleauthor'. There are 25 rows in 1 pages for object 'titleauthor'. DBCC results for 'stores'. There are 6 rows in 1 pages for object 'stores'. Listing distribution ---------------------- distribution Listing master ---------------------- master Listing model ---------------------- model Listing msdb ---------------------- msdb Listing Northwind ---------------------- Northwind Listing pubs ---------------------- pubs Listing rentman ---------------------- rentman Listing tempdb ---------------------- tempdb 

sp_MSforeachtable @command1 @replacechar = '?' [,@command2] [,@command3] [,@whereand] [,@precommand] [,@postcommand]

Executes up to three commands for every table in a database ( optionally matching the @whereand clause). @replacechar will be replaced with the name of each table. @precommand and @postcommand can be used to direct commands to a single result set.

 EXEC sp_MSforeachtable @command1='EXEC sp_help [?]' EXEC sp_MSforeachtable @command1='PRINT "Listing ?"', @command2='SELECT * FROM+ ?',@whereand=' AND name like "title%"' Name            Owner      Type        Created_datetime --------------- ---------- ----------- ----------------------------- Orders          dbo        user table  2000-08-06 01:34:06.610 Column_name     Type       Computed        Length      Prec  Scale N --------------- ---------- --------------- ----------- ----- ----- - OrderID         int        no              4           10    0     n CustomerID      nchar      no              10                      y EmployeeID      int        no              4           10    0     y OrderDate       datetime   no              8                       y RequiredDate    datetime   no              8                       y ShippedDate     datetime   no              8                       y ShipVia         int        no              4           10    0     y Freight         money      no              8           19    4     y ShipName        nvarchar   no              80                      y ShipAddress     nvarchar   no              120                     y ShipCity        nvarchar   no              30                      y ShipRegion      nvarchar   no              30                      y ShipPostalCode  nvarchar   no              20                      y ShipCountry     nvarchar   no              30                      y Identity        Seed      Increment Not For Replication --------------- --------- --------- ------------------- OrderID         1         1         0 RowGuidCol ----------------------------- No rowguidcol column defined. Data_located_on_filegroup ----------------------------- PRIMARY index_name       index_description ---------------- --------------------------------------------------- CustomerID       nonclustered located on PRIMARY CustomersOrders  nonclustered located on PRIMARY EmployeeID       nonclustered located on PRIMARY EmployeesOrders  nonclustered located on PRIMARY OrderDate        nonclustered located on PRIMARY PK_Orders        clustered, unique, primary key located on PRIMARY ShippedDate      nonclustered located on PRIMARY ShippersOrders   nonclustered located on PRIMARY ShipPostalCode   nonclustered located on PRIMARY constraint_type             constraint_name      delete_action updat --------------------------- -------------------- ------------- ----- DEFAULT on column Freight   DF_Orders_Freight    (n/a)         (n/a) FOREIGN KEY                 FK_Orders_Customers  No Action     No Ac FOREIGN KEY                 FK_Orders_Employees  No Action     No Ac FOREIGN KEY                 FK_Orders_Shippers   No Action     No Ac PRIMARY KEY (clustered)     PK_Orders            (n/a)         (n/a) Table is referenced by foreign key ----------------------------------------------------- Northwind.dbo.Order Details: FK_Order_Details_Orders Table is referenced by views ----------------------------------------------------- V1 Listing [dbo].[Order Details] OrderID     ProductID   UnitPrice             Quantity Discount ----------- ----------- --------------------- -------- -------------------- 10248       11          14.0000               12       0.0 10248       42          9.8000                10       0.0 10248       72          34.8000               5        0.0 10249       14          18.6000               9        0.0 10249       51          42.4000               40       0.0 10250       41          7.7000                10       0.0 10250       51          42.4000               35       0.15000001 10250       65          16.8000               15       0.15000001 10251       22          16.8000               6        5.0000001E-2 10251       57          15.6000               15       5.0000001E-2 10251       65          16.8000               20       0.0 10252       20          64.8000               40       5.0000001E-2 10252       33          2.0000                25       5.0000001E-2 10252       60          27.2000               40       0.0 10253       31          10.0000               20       0.0 10253       39          14.4000               42       0.0 10253       49          16.0000               40       0.0 10254       24          3.6000                15       0.15000001 10254       55          19.2000               21       0.15000001 10254       74          8.0000                21       0.0 10255       2           15.2000               20       0.0 10255       16          13.9000               35       0.0 10255       36          15.2000               25       0.0 10255       59          44.0000               30       0.0 10256       53          26.2000               15       0.0 10256       77          10.4000               12       0.0 10257       27          35.1000               25       0.0 10257       39          14.4000               6        0.0 10257       77          10.4000               15       0.0 10258       2           15.2000               50       0.2 Listing [dbo].[Orders] OrderID CustomerID EmployeeID  OrderDate ------- ---------- ----------- ------------------- 10248   VINET      5           1996-07-04 00:00:00 10249   TOMSP      6           1996-07-05 00:00:00 10250   HANAR      4           1996-07-08 00:00:00 10251   VICTE      3           1996-07-08 00:00:00 10252   SUPRD      4           1996-07-09 00:00:00 10253   HANAR      3           1996-07-10 00:00:00 10254   CHOPS      5           1996-07-11 00:00:00 10255   RICSU      9           1996-07-12 00:00:00 10256   WELLI      3           1996-07-15 00:00:00 10257   HILAA      4           1996-07-16 00:00:00 10258   ERNSH      1           1996-07-17 00:00:00 10259   CENTC      4           1996-07-18 00:00:00 10260   OTTIK      4           1996-07-19 00:00:00 10261   QUEDE      4           1996-07-19 00:00:00 10262   RATTC      8           1996-07-22 00:00:00 10263   ERNSH      9           1996-07-23 00:00:00 10264   FOLKO      6           1996-07-24 00:00:00 10265   BLONP      2           1996-07-25 00:00:00 10266   WARTH      3           1996-07-26 00:00:00 10267   FRANK      4           1996-07-29 00:00:00 10268   GROSR      8           1996-07-30 00:00:00 10269   WHITC      5           1996-07-31 00:00:00 10270   WARTH      1           1996-08-01 00:00:00 10271   SPLIR      6           1996-08-01 00:00:00 10272   RATTC      6           1996-08-02 00:00:00 10273   QUICK      3           1996-08-05 00:00:00 10274   VINET      6           1996-08-06 00:00:00 10275   MAGAA      1           1996-08-07 00:00:00 10276   TORTU      8           1996-08-08 00:00:00 10277   MORGK      2           1996-08-09 00:00:00 10278   BERGS      8           1996-08-12 00:00:00 10279   LEHMS      8           1996-08-13 00:00:00 10280   BERGS      2           1996-08-14 00:00:00 10281   ROMEY      4           1996-08-14 00:00:00 10282   ROMEY      4           1996-08-15 00:00:00 10283   LILAS      3           1996-08-16 00:00:00 10284   LEHMS      4           1996-08-19 00:00:00 

sp_MSget_oledbinfo @server [,@infotype] [,@login] [,@password]

Returns OLEDB provider information for a linked server.

 sp_MSget_oledbinfo @server='pythia', @login='sa' 

sp_MSget_qualified_name @object_id, @qualified_name OUT

Translates an object ID into a fully qualified object name.

 DECLARE @oid int, @obname sysname SET @oid=OBJECT_ID('Customers') EXEC sp_MSget_qualified_name @oid, @obname OUT SELECT @obname 

(Results)

 ------------------------------------------------------------------ [dbo].[Customers] 

sp_MSget_type @tabid, @colid, @colname OUT, @type OUT

Returns the name and type of a table column.

 DECLARE @tabid int, @colname sysname, @type nvarchar(4000) SET @tabid=OBJECT_ID('Customers') EXEC sp_MSget_type @tabid, 1, @colname OUT, @type OUT SELECT @colname, @type 

(Results)

 ------------ ---------- CustomerID   nchar(5) 

sp_MSguidtostr @guid, @mystr OUT

Returns a uniqueidentifier as a string.

 DECLARE @guid uniqueidentifier, @guidstr sysname SET @guid=NEWID() EXEC sp_MSguidtostr @guid, @guidstr OUT 

sp_MShelpindex @tablename [,@indexname] [,@flags]

Lists index catalog info. Includes lots of info not returned by the stock sp_helpindex procedure.

 sp_MShelpindex 'Customers' 

(Results abridged)

 name                       status      indid  OrigFillFactor -------------------------- ----------- ------ -------------- PK_Customers               18450       1      0 City                       2097152     2      0 CompanyName                0           3      0 PostalCode                 2097152     4      0 Region                     2097152     5      0 ContactName                2097152     6      0 index_2073058421           2           7      0 _WA_Sys_Country_7B905C75   10485856    8      0 ContactTitle               2097248     9      0 

sp_MShelptype [@typename] [,@flags='sdt''uddt' NULL]

List data type catalog info.

 EXEC sp_MShelptype 'id' EXEC sp_MShelptype 'int','sdt' EXEC sp_MShelptype 

(Results abridged)

 UserDatatypeName      owner   basetypename    defaultname   rulename --------------------- ------- --------------- ------------- ----------- id                    dbo     varchar         NULL          NULL (1 row(s) affected) SystemDatatypeName    ifvarlen_max allownulls  isnumeric   allowidentity --------------------- ------------ ----------- ----------- ------------- int                   NULL         1           0           1 SystemDatatypeName    ifvarlen_max allownulls  isnumeric   allowidentity --------------------- ------------ ----------- ----------- ------------- bigint                NULL         1           0           1 binary                8000         1           0           0 bit                   NULL         1           0           0 char                  8000         1           0           0 datetime              NULL         1           0           0 decimal               NULL         1           1           1 float                 NULL         1           0           0 image                 NULL         1           0           0 int                   NULL         1           0           1 money                 NULL         1           0           0 nchar                 8000         1           0           0 ntext                 NULL         1           0           0 numeric               NULL         1           1           1 nvarchar              8000         1           0           0 real                  NULL         1           0           0 smalldatetime         NULL         1           0           0 smallint              NULL         1           0           1 smallmoney            NULL         1           0           0 sql_variant           NULL         1           0           0 sysname               NULL         0           0           0 text                  NULL         1           0           0 timestamp             NULL         1           0           0 tinyint               NULL         1           0           1 uniqueidentifier      NULL         1           0           0 varbinary             8000         1           0           0 varchar               8000         1           0           0 UserDatatypeName      owner   basetypename   defaultname   rulename  tid --------------------- ------- -------------- ------------- --------- --- empid                 dbo     char           NULL          NULL      259 id                    dbo     varchar        NULL          NULL      257 tid                   dbo     varchar        NULL          NULL      258 

sp_MSindexspace @tablename [,@index_name]

Lists index size info.

 EXEC sp_MSindexspace 'Customers' Index ID Index Name               Size (KB)   Comments -------- ------------------------ ----------- ---------------------------- 1        PK_Customers             16          Size excludes actual data. 2        City                     16          (None) 3        CompanyName              16          (None) 4        PostalCode               16          (None) 5        Region                   16          (None) 6        ContactName              16          (None) 7        index_2073058421         16          (None) 8        _WA_Sys_Country_7B905C75 0           (None) 9        ContactTitle             0           (None) 

sp_MSis_pk_col @source_table, @colname, @indid

Checks a column to see whether it's a primary key.

 DECLARE @res int EXEC @res=sp_MSis_pk_col 'Customers','CustomerId',1 SELECT @res 

(Results)

 ----------- 1 

sp_MSkilldb @dbname

Uses DBCC DBREPAIR to drop a database (even if the database isn't damaged).

 sp_MSkilldb 'northwind2' 

sp_MSloginmappings @loginname

Lists login, database, user, and alias mappings.

 sp_MSloginmappings 

(Results abridged)

 LoginName               DBName        UserName    AliasName ----------------------- ------------- ----------- ----------- BUILTIN\ Administrators NULL          NULL        NULL LoginName               DBName        UserName    AliasName ----------------------- ------------- ----------- ------------ LEX\ TALIONIS           statworld     dbo         NULL LEX\ TALIONIS           Northwind2    dbo         NULL LEX\ TALIONIS           Northwind3    dbo         NULL LEX\ TALIONIS           pubs          dbo         NULL LoginName               DBName        UserName    AliasName ----------------------- ------------- ----------- ------------ distributor_admin       NULL          NULL        NULL LoginName               DBName        UserName    AliasName ----------------------- ------------- ----------- ------------ sa                      distribution  dbo         NULL sa                      master        dbo         NULL sa                      model         dbo         NULL sa                      msdb          dbo         NULL sa                      Northwind     dbo         NULL sa                      tempdb        dbo         NULL sa                      test          dbo         NULL LoginName               DBName        UserName    AliasName ----------------------- ------------- ----------- ------------ puck                    Northwind     puck        NULL puck                    pubs          puck        NULL puck                    pubs2         puck        NULL LoginName               DBName        UserName    AliasName ----------------------- ------------- ----------- ------------ farker                  Northwind     farker      NULL farker                  pubs          farker      NULL farker                  pubs2         farker      NULL LoginName               DBName        UserName    AliasName ----------------------- ------------- ----------- ------------ frank                   Northwind     frank       NULL 

sp_MStable_has_unique_index @tabid

Checks a table for a unique index.

 DECLARE @objid int, @res int SET @objid=OBJECT_ID('Customers') EXEC @res=sp_MStable_has_unique_index @objid SELECT @res 

(Results)

 ----------- 1 

sp_MStablekeys [tablename] [,@colname] [,@type] [,@ keyname ] [,@flags]

Lists a table's keys.

 sp_MStablekeys 'Orders' cType cName                 cFlags      cColCount   cFillFactor ----- --------------------- ----------- ----------- ----------- 1     PK_Orders             1           1           0 3     FK_Orders_Customers   2067        1           NULL 3     FK_Orders_Employees   2067        1           NULL 3     FK_Orders_Shippers    2067        1           NULL 

sp_MStablerefs @tablename,@type= N'actualtables',@direction= N'primary',@reftable

Lists the objects a table references or that reference it.

 sp_MStablerefs 'Orders' 

(Results)

 candidate_table    candidate_key  referenced ------------------ -------------- ---------- [dbo].[Customers]  N/A            1 [dbo].[Employees]  N/A            1 [dbo].[Shippers]   N/A            1 

sp_MStablespace [@name]

Lists table space information.

 sp_MStablespace 'Orders' 

(Results)

 Rows        DataSpaceUsed IndexSpaceUsed ----------- ------------- -------------- 830         208           328 

sp_MSunc_to_drive @unc_path, @local_server, @local_path OUT

Converts a UNC path to a drive.

 DECLARE @path sysname EXEC sp_MSunc_to_drive '\ \ PYTHIA\ C$\ ', 'PYTHIA',@path OUT SELECT @path 

(Results)

 --------------------------------------------------------------------------- C:\ 

sp_MSuniquecolname table_name, @base_colname, @unique_colname OUT

Generates a unique column name for a specified table using a base name.

 DECLARE @uniquename sysname EXEC sp_MSuniquecolname 'Customers','CustomerId',@uniquename OUT SELECT @uniquename 

(Results)

 --------------------------------------------------------------------------- CustomerId13 

sp_MSuniquename @seed, @start

Returns a result set containing a unique object name for the current database using a specified seed name and start value.

 sp_MSuniquename 'Customers',3 Name                                              Next ------------------------------------------------- ----------- Customers3__92 

sp_MSuniqueobjectname @name_in, @name_out OUT

Generates a unique object name for the current database.

 DECLARE @outname sysname SET @outname='' -- Can't be NULL EXEC sp_MSuniqueobjectname 'Customers',@outname OUT SELECT @outname 

(Results)

 --------------------------------------------------------------------------- austomers 

sp_MSuniquetempname @name_in, @name_out OUT

Generates a unique temporary object (tempdb) name using a base name.

 USE tempdb CREATE TABLE livr_kp (c1 int) DECLARE @name_out sysname exec sp_Msuniquetempname 'livr_kp', @name_out OUT SELECT @name_out 

(Results)

 ------------------------------------------------- liar_kp 

sp_readerrorlog [@lognum]

Lists the system error log corresponding to lognum. Omit lognum to list the current error log.

 sp_readerrorlog 2 

(Results abridged)

 ERRORLOG.2 --------------------------------------------------------------- 2000-09-29 22:57:38.89 server    Microsoft SQL Server  2000 - 8         Aug  6 2000 00:57:48         Copyright (c) 1988-2000 Microsoft Corporation         Personal Edition on Windows NT 5.0 (Build 2195: Service 2000-09-29 22:57:38.96 server    Copyright (C) 1988-2000 Micros 2000-09-29 22:57:38.96 server    All rights reserved. 2000-09-29 22:57:38.96 server    Server Process ID is 780. 2000-09-29 22:57:38.96 server    Logging SQL Server messages in 2000-09-29 22:57:45.73 server    SQL server listening on TCP, S 2000-09-29 22:57:45.73 server    SQL server listening on 192.16 2000-09-29 22:57:45.81 server    SQL Server is ready for client 2000-09-29 22:57:45.90 spid5     Clearing tempdb database. 2000-09-29 22:57:49.06 spid5     Starting up database 'tempdb'. 2000-09-29 22:57:51.07 spid4     Recovery complete. 

sp_remove_tempdb_file @filename

Removes a file on which tempdb is based.

 master..sp_remove_tempdb_file 'tempdev02' 

sp_set_local_time [@server_name] [,@adjustment_in_minutes] (for Win9x)

Synchronizes the computer's local time with another server (if supplied).

 msdb..sp_set_local_time 

sp_tempdbspace

Returns space usage info for tempdb.

 sp_tempdbspace 

(Results)

 database_name database_size           spaceused ------------- ----------------------- ------------------------------------- tempdb        8.750000                .546875 

xp_dirtree 'rootpath'

Completely lists all the subdirectories (and their subdirectories) of a given path, including the node level of each directory.

 master..xp_dirtree 'c:\ ' 

(Results abridged)

 subdirectory      depth ----------------- ----------- WINDOWS           1 SYSTEM            2 OOBE              3 MSNSETUP          4 SETUP             4 HTML              4 MOUSE             5 IMAGES            6 ISPSGNUP          5 IMAGES            4 ERROR             4 MSNHTML           4 ISPSGNUP          5 MOUSE             5 MSNERROR          4 MSN               4 PASSPORT          4 SHELLEXT          3 COLOR             3 VMM32             3 MACROMED          3 DIRECTOR          4 FLASH             4 Shockwave         4 XTRAS             5 IOSUBSYS          3 VIEWERS           3 WBEM              3 logs              4 MOF               4 bad               5 good              5 

xp_dsninfo @systemdsn

Lists ODBC DSN information for the specified system datasource.

 master..xp_dsninfo 'pubsdsn' 

xp_enum_oledb_providers

Enumerates the OLEDB providers available on the server machine.

 master..xp_enum_oledb_providers 

(Results abridged)

 Provider Name           Provider Description ----------------------- --------------------------------------------------- EMPOLEDB.1              VSEE Versioning Enlistment Manager Proxy Data Source MediaCatalogDB.1        MediaCatalogDB OLE DB Provider SQLOLEDB                Microsoft OLE DB Provider for SQL Server DTSPackageDSO           Microsoft OLE DB Provider for DTS Packages SQLReplication.OLEDB    SQL Server Replication OLE DB Provider for DTS MediaCatalogMergedDB.1  MediaCatalogMergedDB OLE DB Provider MSDMine                 Microsoft OLE DB Provider For Data Mining Services ADsDSOObject            OLE DB Provider for Microsoft Directory Services MediaCatalogWebDB.1     MediaCatalogWebDB OLE DB Provider MSDAIPP.DSO             Microsoft OLE DB Provider for Internet Publishing MSSearch.CollatorDSO.1  Microsoft OLE DB Provider for Microsoft Search MSDASQL                 Microsoft OLE DB Provider for ODBC Drivers MSUSP                   Microsoft OLE DB Provider for Outlook Search Microsoft.Jet.OLEDB.4.0 Microsoft Jet 4.0 OLE DB Provider MSDAOSP                 Microsoft OLE DB Simple Provider MSDAORA                 Microsoft OLE DB Provider for Oracle MSIDXS                  Microsoft OLE DB Provider for Indexing Service 

xp_enumdsn

Enumerates the system ODBC datasources available on the server machine.

 master..xp_enumdsn 

(Results abridged)

 Data Source Name       Description ---------------------- ----------------------------------- DeluxeCD               Microsoft Access Driver (*.mdb) Visual FoxPro Database Microsoft Visual FoxPro Driver Visual FoxPro Tables   Microsoft Visual FoxPro Driver dBase Files - Word     Microsoft dBase VFP Driver (*.dbf) FoxPro Files - Word    Microsoft FoxPro VFP Driver (*.dbf) SS7                    SQL Server KHENSS2K               SQL Server MS Access Database     Microsoft Access Driver (*.mdb) Excel Files            Microsoft Excel Driver (*.xls) dBASE Files            Microsoft dBase Driver (*.dbf) LocalServer            SQL Server MQIS                   SQL Server FoodMart               Microsoft Access Driver (*.mdb) ECDCMusic              Microsoft Access Driver (*.mdb) 

xp_enumerrorlogs

Enumerates (lists) the current server error log files.

 master..xp_enumerrorlogs 

(Results abridged)

 Archive # Date                Log File Size (Byte) --------- ------------------- -------------------- 6         06/28/2000  23:13   3139 5         06/29/2000  11:19   3602 4         06/29/2000  11:35   3486 3         06/29/2000  22:55   15998 2         06/29/2000  23:10   3349 1         07/01/2000  12:49   120082 0         07/01/2000  12:51   31086 

xp_execresultset 'code query','database'

Allows you to supply a query that returns a T-SQL query to execute. This is handy for extremely large queriesthose too large for varchar(8,000) variables . You can simply place your query in a table and reference the table in the query you pass to xp_execresultset:

 exec master..xp_execresultset 'SELECT ''PRINT ''test''','pubs' 

(Results)

 test 

xp_fileexist 'filename'

Returns a result set indicating whether a file exists.

 exec master..xp_fileexist 'd:\ winnt\ readme.txt' exec master..xp_fileexist 'c:\ winnt\ readme.txt' exec master..xp_fileexist 'c:\ winnt\ odbc.ini' exec master..xp_fileexist 'c:\ winnt' 

(Results)

 File Exists File is a Directory Parent Directory Exists ----------- ------------------- ----------------------- 0           0                   0 File Exists File is a Directory Parent Directory Exists ----------- ------------------- ----------------------- 0           0                   1 File Exists File is a Directory Parent Directory Exists ----------- ------------------- ----------------------- 1           0                   1 File Exists File is a Directory Parent Directory Exists ----------- ------------------- ----------------------- 0           1                   1 

xp_fixeddrives

Returns a result set listing the fixed drives on the server machine.

 master..xp_fixeddrives 

(Results)

 drive MB free ----- ----------- C     4743 

xp_get_MAPI_default_profile

Returns the default MAPI mail profile.

 master..xp_get_MAPI_default_profile 

(Results)

 Profile name -------------------------------- Microsoft Outlook Internet Setti 

xp_get_MAPI_profiles

Returns a result set listing the system's MAPI profiles.

 master..xp_get_MAPI_profiles 

(Results)

 Profile name                     Is default profile -------------------------------- ------------------ Microsoft Outlook Internet Setti 1 

xp_getfiledetails 'filename'

Returns a result set listing file details for the specified file.

 master..xp_getfiledetails 'c:\ winnt\ odbc.ini' 

(Results abridged)

 Alternate Name Size   Creation Date Creation Time Last Written Date -------------- ------ ------------- ------------- ----------------- NULL           2144   20000903      220228        20000628 

xp_getnetname

Returns the network name of the server computer.

 master..xp_getnetname 

(Results)

 Server Net Name --------------- TALIONIS 

xp_oledbinfo @providername, @datasource, @location, @providerstring, @catalog, @login, @password, @infotype

Returns a result set listing detailed OLEDB information about a specific linked server.

 master..xp_oledbinfo 'SQLOLEDB', 'PYTHIA', NULL, NULL, NULL, 'sa', 'drkildare', NULL 

(Results)

 Information Type                 Value -------------------------------- ------------------------------ DBMS Name                        Microsoft SQL Server DBMS Version                     8.00.194 Database Name                    master SQL Subscriber                   TRUE 

xp_readerrorlog [lognum][filename]

Returns a result set (c1 char(255) c2 int) containing the error log specified by lognum (omit to get the current error log).

 master..xp_readerrorlog 3 

(Results abridged)

 ERRORLOG.3 ------------------------------------------------------------------------- 2000-09-29 11:36:07.58 server    Microsoft SQL Server  2000 - 8.00.194 (I         Aug  6 2000 00:57:48         Copyright (c) 1988-2000 Microsoft Corporation         Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 2, R 2000-09-29 11:36:07.58 server    Copyright (C) 1988-2000 Microsoft Corpor 2000-09-29 11:36:07.60 server    All rights reserved. 2000-09-29 11:36:07.60 server    Server Process ID is 1080. 2000-09-29 11:36:07.60 server    Logging SQL Server messages in file 'C:\ 2000-09-29 11:36:07.61 server    SQL Server is starting at priority class 2000-09-29 11:36:07.72 server    SQL Server configured for thread mode pr 2000-09-29 11:36:07.72 server    Using dynamic lock allocation. [500] Loc 2000-09-29 11:36:07.85 spid3     Starting up database 'master'. 2000-09-29 11:36:07.99 server    Using 'SSNETLIB.DLL' version '8.0.194'. 2000-09-29 11:36:07.99 spid5     Starting up database 'model'. 2000-09-29 11:36:08.02 spid3     Server name is 'KHENMP\ SS2000'. 2000-09-29 11:36:08.02 spid3     Skipping startup of clean database id 4 2000-09-29 11:36:08.02 spid3     Skipping startup of clean database id 5 2000-09-29 11:36:08.18 spid5     Clearing tempdb database. 2000-09-29 11:36:08.51 spid5     Starting up database 'tempdb'. 2000-09-29 11:36:08.71 spid3     Recovery complete. 2000-09-29 22:55:28.36 server    SQL Server terminating because of system 2000-09-29 22:55:39.34 spid3     SQL Server is terminating due to 'stop' 

You can also pass -1 as lognum and specify a second parameter containing the name of a file you want to read instead of an error log. That is, xp_reader rorlog can read any text file, not just error logs. For example, this command will read a file named README.TXT:

 EXEC master..xp_readerrorlog -1, 'C:\ README.TXT' 

xp_regenumvalues

Enumerates the values under a registry key.

 CREATE TABLE #reg (kv nvarchar(255) NOT NULL,  kvdata nvarchar(255) null) INSERT #reg EXEC master..xp_regenumvalues 'HKEY_LOCAL_MACHINE', 'SOFTWARE\ Microsoft\ MSSQLServer\ MSSQLServer' SELECT * FROM #reg 

(Results)

 kv                    kvdata --------------------- ----------------------------------------------------- FullTextDefaultPath   C:\ Program Files\ Microsoft SQL Server\ MSSQL$SS2000\ FTData ListenOn - Item #1    SSMSSH70 ListenOn - Item #2    SSNETLIB SetHostName           0 AuditLevel            0 LoginMode             2 Tapeloadwaittime      -1 DefaultLogin          guest Map_                  \ Map#                  - Map$                  NULL BackupDirectory       C:\ Program Files\ Microsoft SQL Server\ MSSQL$SS2000\ BACKUP DefaultDomain         DAD DefaultCollationName  SQL_Latin1_General_CP1_CI_AS 

xp_regaddmultistr, xp_regdeletekey, xp_regdeletevalue, xp_regread, xp_regremovemultistring, xp_regwrite

Allows addition, modification, and deletion of registry keys and key values.

 DECLARE @df nvarchar(64) EXEC master.dbo.xp_regread N'HKEY_CURRENT_USER', N'Control   Panel\ International', N'sShortDate', @df OUT, N'no_output' SELECT @df 

(Results)

 ---------------------------------------------------------------- M/d/yyyy 

xp_subdirs

Returns a result set containing a directory's immediate subdirectories.

 master..xp_subdirs 'C:\ Program Files\ Microsoft SQL Server' 

(Results)

 subdirectory -------------- MSSQL$SS2000 80 

xp_test_MAPI_profile 'profile'

Tests the specified MAPI profile to ensure that it's valid and can be connected to.

 master..xp_test_MAPI_profile 'SQL' 

xp_varbintohexstr

Converts a varbinary variable to a hexadecimal string.

 CREATE PROC sp_hex @i int, @hx varchar(30) OUT AS DECLARE @vb varbinary(30) SET @vb=CAST(@i as varbinary) EXEC master..xp_varbintohexstr @vb, @hx OUT GO DECLARE @hex varchar(30) EXEC sp_hex 343, @hex OUT SELECT @hex 

(Results)

 ------------------------------ 0x00000157 

for RuBoard