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:
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.
Some undocumented routines are so fragile and add so little value to the Transact-SQL command set that they are best left undocumented.
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.
Checks key system tables for non-ASCII names.
sp_checknames @mode=''silent''
Clears system backup history prior to a given date.
msdb..sp_delete_backuphistory @oldest_date datetime
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
Enumerates the OLEDB data providers visible to the server.
sp_enumoledbdatasources
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
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)
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''
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
Adds a ROWGUIDCOL column to a table. Also marks the table for replication (use EXEC sp_MSunmarkreplinfo to reverse this).
sp_MSaddguidcolumn dbo,testguid
Creates an index on a table''s ROWGUIDCOL column.
sp_MSaddguidindex dbo,testuid
Adds a SQL Server login that corresponds to an existing NT login.
sp_MSaddlogin_implicit_ntlogin ''GoofyTingler''
Adds a database user that corresponds to an existing NT login.
sp_MSadduser_implicit_ntlogin ''GoofyTingler''
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
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
Returns a bitmap representing user privileges.
sp_MSdbuserpriv @mode=''role''
(Results)
----------- 73855
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
Generically drops a table, view, trigger, or procedure.
sp_MSdrop_object @object_name=''authors2''
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''
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
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
Returns OLEDB provider information for a linked server.
sp_MSget_oledbinfo @server=''pythia'', @login=''sa''
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]
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)
Returns a uniqueidentifier as a string.
DECLARE @guid uniqueidentifier, @guidstr sysname SET @guid=NEWID() EXEC sp_MSguidtostr @guid, @guidstr OUT
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
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
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)
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
Uses DBCC DBREPAIR to drop a database (even if the database isn''t damaged).
sp_MSkilldb ''northwind2''
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
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
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
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
Lists table space information.
sp_MStablespace ''Orders''
(Results)
Rows DataSpaceUsed IndexSpaceUsed ----------- ------------- -------------- 830 208 328
Converts a UNC path to a drive.
DECLARE @path sysname EXEC sp_MSunc_to_drive ''\ \ PYTHIA\ C$\ '', ''PYTHIA'',@path OUT SELECT @path
(Results)
----------------------------------- C:
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
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
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
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
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.
Removes a file on which tempdb is based.
master..sp_remove_tempdb_file ''tempdev02''
Synchronizes the computer''s local time with another server (if supplied).
msdb..sp_set_local_time
Returns space usage info for tempdb.
sp_tempdbspace
(Results)
database_name database_size spaceused ------------- ----------------------- ---------- tempdb 8.750000 .546875
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
Lists ODBC DSN information for the specified system datasource.
master..xp_dsninfo ''pubsdsn''
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
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)
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
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
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
Returns a result set listing the fixed drives on the server machine.
master..xp_fixeddrives
(Results)
drive MB free ----- ----------- C 4743
Returns the default MAPI mail profile.
master..xp_get_MAPI_default_profile
(Results)
Profile name -------------------------------- Microsoft Outlook Internet Setti
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
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
Returns the network name of the server computer.
master..xp_getnetname
(Results)
Server Net Name --------------- TALIONIS
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
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''
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
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
Returns a result set containing a directory''s immediate subdirectories.
master..xp_subdirs ''C:\ Program Files\ Microsoft SQL Server''
(Results)
subdirectory -------------- MSSQL$SS2000 80
Tests the specified MAPI profile to ensure that it''s valid and can be connected to.
master..xp_test_MAPI_profile ''SQL''
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