لطفا منتظر باشید ...
Undocumented Procedures
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.
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
