On the Journey to be a SQL Server Professional: The Undocumented Stored Procedures and Other Hidden Features in SQL Server

1. List of Undocumented Stored Procedures in SQL Server

(http://www.sqlservercurry.com/2010/04/list-of-undocumented-stored-procedures.html)

(The highlighted have description, syntax and example below)

sp_checknames

sp_columns_rowset

sp_enumoledbdatasources

sp_fixindex

sp_gettypestring

sp_ms_marksystemobject

sp_msaddguidcolumn

sp_msaddguidindex

sp_msaddlogin_implicit_ntlogin

sp_msadduser_implicit_ntlogin

sp_mscheck_uid_owns_anything

sp_msdbuseraccess

sp_msdbuserpriv

sp_msdependencies

sp_msdrop_object

sp_msforeachdb

sp_msforeachtable

sp_msget_qualified_name

sp_msgettools_path

sp_msgetversion

sp_msguidtostr

sp_mshelpcolumns

sp_mshelpindex

sp_mshelptype

sp_msindexspace

sp_msis_pk_col

sp_mskilldb

sp_msloginmappings

sp_mstablekeys

sp_mstablerefs

sp_mstablespace

sp_msunc_to_drive

sp_msuniquecolname

sp_msuniquename

sp_msuniqueobjectname

sp_msuniquetempname

sp_tempdbspace

sp_who2

xp_availablemedia(2005)

xp_create_subdir(2005)

xp_delete_file

xp_dirtree

xp_enum_oledb_providers(2005)

xp_enumcodepages (2005)

xp_enumdsn

xp_enumerrorlogs(2005)

xp_enumgroups

xp_fileexist (2005)

xp_fixeddrives (2005)

xp_get_mapi_default_profile

xp_get_mapi_profiles

xp_getnetname (2005)

xp_get_tape_devices (2005)

xp_qv

xp_readerrorlog

xp_regaddmultistring

xp_regdeletekey

xp_regdeletevalue

xp_regenumvalues

xp_regread

xp_regremovemultistring

xp_regwrite

xp_subdirs

xp_test_mapi_profile (2005)

xp_varbintohexstr

2. Source 1 - Useful undocumented extended stored procedures in details

Alexander Chigrik

chigrik@mssqlcity.com

http://www.mssqlcity.com/Articles/Undoc/UndocExtSP.htm

An extended stored procedure (xp) is a dynamic link library that runs directly in the address space of SQL Server and is programmed using the SQL Server Open Data Services API. You can run extended stored procedures from the Query Analyzer, for example, just as you would normal stored procedures. Extended stored procedures are used to extend the capabilities of SQL Server. You can take advantage of the many extended stored procedures that come with SQL Server, or you can write your own in a programming language such as C or C++.

In this article, I want to tell you about some useful undocumented extended stored procedures. These extended stored procedures work with SQL Server 7.0, as well as with SQL Server 2000.

sp_MSgetversion

This extended stored procedure can be used to get the current version of Microsoft SQL Server. To get the current SQL Server version, run

EXEC master..sp_MSgetversion

Note. A more common way to retrieve the current SQL Server version (this way provides more information) is to use following SELECT statement:

SELECT @@version

xp_dirtree

This extended stored procedure can be used to get a list of all the folders for the folder named in the xp. To get a list of all the folders in the C:\MSSQL7 folder, run:

EXEC master..xp_dirtree 'C:\MSSQL7'

xp_enum_oledb_providers

This extended stored procedure is used to list of all the available OLE DB providers. It returns Provider Name, Parse Name and Provider Description. To get a list of all OLE DB providers for your SQL Server, run:

EXEC master..xp_enum_oledb_providers

xp_enumcodepages

This extended stored procedure can be used to list of all code pages, character sets and their description for your SQL Server. To get a list of all code pages and character sets, run:

EXEC master..xp_enumcodepages

xp_enumdsn

This extended stored procedure returns a list of all System DSNs and their description. To get the list of System DSNs, run:

EXEC master..xp_enumdsn

xp_enumerrorlogs

This extended stored procedure returns the list of all error logs with their last change date. To get the list of error logs, run:

EXEC master..xp_enumerrorlogs

xp_enumgroups

This extended stored procedure returns the list of Windows NT groups and their description. To get the list of the Windows NT groups, run:

EXEC master..xp_enumgroups

xp_fileexist

You can use this extended stored procedure to determine whether a particular file exists on the disk or not.

Syntax:

EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]

For example, to check whether the file boot.ini exists on disk c: or not, run:

EXEC master..xp_fileexist 'c:\boot.ini'

xp_fixeddrives

This very useful extended stored procedure returns the list of all hard drives and the amount of free space in Mb for each hard drive.

To see the list of drives, run:

EXEC master..xp_fixeddrives

xp_getnetname

This extended stored procedure returns the WINS name of the SQL Server that you're connected to.

To view the name, run:

EXEC master..xp_getnetname

xp_readerrorlog

This extended stored procedure returns the content of the errorlog file. You can find the errorlog file in the C:\MSSQL7\Log directory, by default for SQL Server 7.0.

To see the text of the errorlog file, run:

EXEC master..xp_readerrorlog

xp_regdeletekey

This extended stored procedure will delete an entire key from the registry. You should use it very carefully.

Syntax:

EXECUTE xp_regdeletekey [@rootkey=]'rootkey',[@key=]'key'

For example, to delete the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regdeletekey

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test'

xp_regdeletevalue

This extended stored procedure will delete a particular value for a key in the registry. You should use it very carefully.

Syntax:

EXECUTE xp_regdeletevalue [@rootkey=]'rootkey',

[@key=]'key',[@value_name=]'value_name'

For example, to delete the value 'TestValue' for the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regdeletevalue

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test',

@value_name='TestValue'

xp_regread

This extended stored procedure is used to read from the registry.

Syntax:

EXECUTE xp_regread [@rootkey=]'rootkey',

[@key=]'key'

[, [@value_name=]'value_name']

[, [@value=]@value OUTPUT]

For example, to read into the variable @test from the value 'TestValue' from the key 'SOFTWARE\Test' from the 'HKEY_LOCAL_MACHINE', run:

DECLARE @test varchar(20)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test',

@value_name='TestValue',

@value=@test OUTPUT

SELECT @test

xp_regwrite

This extended stored procedure is used to write to the registry.

Syntax:

EXECUTE xp_regwrite [@rootkey=]'rootkey',

[@key=]'key',

[@value_name=]'value_name',

[@type=]'type',

[@value=]'value'

For example, to write the variable 'Test' to the 'TestValue' value, key 'SOFTWARE\Test', 'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regwrite

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test',

@value_name='TestValue',

@type='REG_SZ',

@value='Test'

xp_subdirs

This extended stored procedure is used to get the list of folders for the folder named in the xp. In comparison with xp_dirtree, xp_subdirs returns only those directories whose depth = 1.

This is the example:

EXEC master..xp_subdirs 'C:\MSSQL7'

3. Source 2 - Undocumented Extended and Stored Procedures in SQL 2005

By Timothy A Wiseman, 2009/11/20 (first published: 2008/05/09)

(http://www.sqlservercentral.com/articles/Stored+Procedures/62868/)

Introduction

There are several undocumented system stored procedures and extended stored procedures in SQL Server 2005 which can be of enormous use. Since they are undocumented, it can be risky to rely upon them. There is always the possibility that they will be dropped or altered, which can be worse, with the next version of SQL Server or even the next service pack. If it is important that the code in question works with the next version of SQL Server then it is wise to avoid undocumented procedures. But they can be more than worth that small risk when compatibility with future versions is not a priority.

XP_FileExist

The usage is:

EXEC xp_fileexist <filename> [, <file_exists INT> OUTPUT]

If executed without providing an output variable, it will display 3 columns indicating whether the file passed in exists, whether it is a directory, and if the parent directory exists. For instance:

exec master.dbo.xp_fileexist 'C:\temp'

Results in this output:

xp_fileexist Output
File Exists	File is a Directory	Parent Directory Exists
0	1	1

If it is executed with the output parameter it will set the value to one if the file exists and 0 otherwise. It will set the variable to 0 if the name given is a directory.

Generally, it is better to check validate the existence of any files prior to calling the T-SQL script, but there are certainly times when it is convenient to be able to do it from within a T-SQL script. This syntax is far more graceful than work arounds which can be achieved with xp_cmdshell.

SP_MSForEachDb

sp_msforeachdb will execute the attached string as a command against every database on the server. Any question marks (?), within the string will be replaced by every database name. It can be useful for finding the database which holds a given table on a server. For example:

exec dbo.sp_MSforeachdb 'select ''?'', * from [?].INFORMATION_SCHEMA.TABLES where table_name like ''authors'' '

will find every table named authors on the server. It can also be useful for running maintenance routines against every database. For instance, this:

exec dbo.sp_MSforeachdb 'DBCC SHRINKDATABASE(N''?'' )'

will shrink the file size of every database on the server. It may be worth noting that on a well designed production system it would not be a good idea to run that command, but it can be useful for reclaiming file space on test and development servers and illustrates the broader ways it can be used for administrative commands when needed.

SP_MSForEachTable

sp_msforeachtable is very similar to sp_msforeachdb, except executing the command against every table in the database. For instance, if there is a test database and all tables in the test database need to emptied while retaining the table structures, this command could be used:

exec dbo.sp_msforeachtable 'delete test.dbo.[?]'

SP_who2

Sp_who2 is like sp_who's bigger brother. It functions in a very similar fashion to the documented procedure sp_who except that some of the column names are slightly changed, the ecid column is dropped, and some additional columns are listed. It adds the cputime, diskio, lastbatch, and programname columns to the information provided by sp_who. Its output appears as:

sp_who2
SPID	Status	Login	H.N.	BlkBy	DBNm	Command	CPUTm	DiskIO	LastBatch	PrgNm	SPID	REQID
1	Bkgd	sa	.	.	NULL	RESOURCE MONITOR	100	0	04/12 15:58:54		1	0
2	Bkgd	sa	.	.	NULL	LAZY WRITER	10	0	04/12 15:58:54		2	0
3	Susp	sa	.	.	NULL	LOG WRITER	0	0	04/12 15:58:54		0	0
4	Bkgd	sa	.	.	NULL	LOCK MONITOR	0	0	04/12 15:58:54		4	0

Both sp_who and sp_who2 with its added columns can be tremendously valuable in scripts and programs, but for use on an ad hoc basis it is often more convenient to invoke the Activity Monitor GUI.

sp_MSdependencies

sp_MSdependencies is a powerful procedure that can be used to determine all dependencies for an object within a database. Its usage is:

exec dbo.sp_MSdependencies [<object name> ] , [<object type>], [<Flags>], [<objlist>]

The procedure also accepts an integer variable @intrans, but it does nothing. All of the variables are optional and in most cases it is not necessary to pass both an object name and an object type. If

exec sp_msdependencies '?'

is executed it will provide a brief description of the usage options.

sp_MSdependencies is similar to the documented stored procedure sp_depends, but it is simultaneously more flexible and more difficult to use. sp_depends will return two result sets, the first lists those things that the targeted object depends on directly and the second listing those things which depend directly on the targeted object, and in both cases it does not consider foreign key relationships to be dependencies. sp_MSdependencies on the other hand does count foreign key relationships and will return either things which depend on the target or on which the target depends and precisely what it returns is determined by the flags passed. By default, it will not return system tables or other internal objects or user defined data types, but it can be set to do so by using the flags switch. It also formats its results different from sp_depends.

If it is executed with only an object name, it will return everything on which that object depends. For instance:

exec sp_msdependencies 'dbo.titleview'

exected in the pubs database returns

sp_msdependencies output
oType	oObjName	oOwner	oSequence
8	authors	dbo	1
8	publishers	dbo	1
8	titles	dbo	2
8	titleauthor	dbo	3

Which indicates that dbo.titleview depends on authors, titles, and titleauthor while authors depends on the publishers table. The oType of 8 indicates that they are all tables.

Providing only an object type by number will result in a list of everything which depends directly or indirectly on any object of that type within the current database. In the sample pubs database, the dbo.titleview is the only view, so if views were selected and

exec sp_msdependencies NULL, 2

were executed the results would be

oType oObjName oOwner oSequence

----------- ------------------------ ------------------------------- ---------

8 authors dbo 1

8 publishers dbo 1

8 titles dbo 2

8 titleauthor dbo 3

4 titleview dbo 4

Providing everything which any view depends on, including the view itself.

The flags can be added to either an object name or an object type and they determine precisely what is returned. The flags parameter is a bitmap expressed in hexadecimal. It permits precise control over what types of objects are returned and will allow the return of children, or objects which depend on the target, instead of parents, or objects on which the target depends. Using the flags, it can be set to return the system objects and user defined data types which are normally skipped. It can also be set to return only first level relationships which makes it more like sp_depends. For instance, to retrieve the objects which depend on the titles table execute:

exec sp_msdependencies 'titles', NULL, 0x401fd

which returns:

sp_msdependencies output
oType	oObjName	oOwner	oSequence
8	roysched	dbo	1
8	sales	dbo	1
8	titleauthor	dbo	1
16	reptq1	dbo	1
16	reptq2	dbo	1
16	reptq3	dbo	1
4	titleview	dbo	2
16	byroyalty	dbo	2

Everything on the list depends either directly or indirectly on the titles table. For instance, the procedure byroyalty does not directly rely on the titles table, but it relies on titleview which in turn is dependant on the titles table.

In general, it is more convenient and user friendly to simply use the GUI provided in the SSMS to gather the dependencie information when it is needed on an ad hoc basis, but sp_msdepencies can be invaluable in creating scripts in which dependencies need to be evaluated. Its ability to consider foreign key relationships and indirect dependencies can also make it valuable in situations where the documented sp_depends is not appropriate.

Conclusion

Undocumented procedures should always be used cautiously, especially when dealing with procedures that are expected to be able to migrate from one version of SQL server to another. Undocumented procedures are far more likely than their documented counterparts to be changed, and they will not be officially supported if technical support is ever required. With those risks in mind, they can often be tremendously useful. This provides a short list of some of the more useful ones and how they can be used in SQL Server 2005.

4. Source 3 - Some SQL Server 2000 useful undocumented stored procedures

Alexander Chigrik

chigrik@mssqlcity.com

(http://mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm)

In this article, I want to tell you about some useful undocumented stored procedures shipped with SQL Server 2000.

sp_MSget_qualified_name

The sp_MSget_qualified_name stored procedure is used to get the qualified name for the given object id.

Syntax

sp_MSget_qualified_name object_id, qualified_name

where

object_id - is the object id. object_id is int.

qualified_name - is the qualified name of the object. qualified_name

is nvarchar(512).

This is the example to get the qualified name for the authors table from the pubsdatabase.

USE pubs

declare @object_id int, @qualified_name nvarchar(512)

select @object_id = object_id('authors')

EXEC sp_MSget_qualified_name @object_id, @qualified_name output

select @qualified_name

Here is the result set from my machine:

--------------------------------------

[dbo].[authors]

sp_MSdrop_object

The sp_MSdrop_object stored procedure is used to drop an object (it can be table, view, stored procedure or trigger) for the given object id, object name, and object owner. If object id, object name, and object owner are not specified, then nothing will be dropped.

Syntax

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

where

object_id - is the object id. object_id is int,

with a default of NULL.

object_name - is the name of the object. object_name is sysname,

with a default of NULL.

object_owner - is the object owner. object_owner is sysname,

with a default of NULL.

This is the example of dropping the titleauthor table from the pubs database.

USE pubs

declare @object_id int

select @object_id = object_id('titleauthor')

EXEC sp_MSdrop_object @object_id

sp_gettypestring

The sp_gettypestring stored procedure returns the type string for the given table id and column id.

Syntax

sp_gettypestring tabid, colid, typestring

where

tabid - is the table id. tabid is int.

colid - is the column id. colid is int.

typestring - is the type string. It's output parameter.

typestring is nvarchar(255).

This is the example to get the type string for the column number 2 in the authors table, from the pubs database.

USE pubs

declare @tabid int, @typestring nvarchar(255)

select @tabid = object_id('authors')

EXEC sp_gettypestring @tabid, 2, @typestring output

select @typestring

Here is the result set from my machine:

-------------------------------

varchar(40)

sp_MSgettools_path

The sp_MSgettools_path stored procedure returns the path to the SQL Server 2000 tools and utilities.

Syntax

sp_MSgettools_path install_path

where

install_path - is the installation path. It's output parameter.

install_path is nvarchar(260).

This is the example to get the path to the SQL Server 2000 tools and utilities.

USE master

declare @install_path NVARCHAR(260)

EXEC sp_MSgettools_path @install_path OUTPUT

select @install_path

Here is the result set from my machine:

----------------------------------------------------------

C:\Program Files\Microsoft SQL Server\80\Tools

sp_MScheck_uid_owns_anything

The sp_MScheck_uid_owns_anything stored procedure returns the list of the object, owned by the specified user.

Syntax

sp_MScheck_uid_owns_anything uid

where

uid - is the User ID, unique in this database. uid is smallint.

This is the example to get the list of the objects, owned by the database owner 1 in thepubs database.

USE pubs

EXEC sp_MScheck_uid_owns_anything 1

sp_columns_rowset

The sp_columns_rowset stored procedure returns the complete columns description, including the length, type, name, and so on.

Syntax

sp_columns_rowset table_name [, table_schema ] [, column_name]

where

table_name - is the table name. table_name is sysname.

table_schema - is the table schema. table_schema is sysname,

with a default of NULL.

column_name - is the column name. column_name is sysname,

with a default of NULL.

This is the example:

USE pubs

EXEC sp_columns_rowset 'authors'

sp_fixindex

The sp_fixindex stored procedure can be used to fix corruption in a system table by recreating the index.

Syntax

sp_fixindex dbname, tabname, indid

where

dbname - is the database name. dbname is sysname.

tabname - is the system table name. tabname is sysname.

indid - is the index id value. indid is int

Note. Before using this stored procedure the database has to be in single user mode.

See this link for more information:

How can I fix a corruption in a system table?

This is the example:

USE pubs

EXEC sp_fixindex pubs, sysindexes, 2

sp_MSforeachdb

Sometimes, you need to perform the same actions for all databases. You can create cursor for this purpose, or you can also use the sp_MSforeachdb stored procedure to accomplish the same goal with less work.

For example, you can use the sp_MSforeachdb stored procedure to run a CHECKDB for all the databases on your server:

EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"

sp_MSforeachtable

Sometimes, you need to perform the same actions for all tables in the database. You can create cursor for this purpose, or you can also use the sp_MSforeachtable stored procedure to accomplish the same goal with less work.

For example, you can use the sp_MSforeachtable stored procedure to rebuild all the indexes in a database:

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

sp_MShelpcolumns

The sp_MShelpcolumns stored procedure returns the complete schema for a table, including the length, type, name, and whether a column is computed.

Syntax

sp_MShelpcolumns tablename [, flags] [, orderby] [, flags2]

where

tablename - is the table name. tablename is nvarchar(517).

flags - flags is int, with a default of 0.

orderby - orderby is nvarchar(10), with a default of NULL.

flags - flags2 is int, with a default of 0.

To get the full columns description for the authors table in the pubs database, run:

USE pubs

EXEC sp_MShelpcolumns 'authors'

sp_MShelpindex

The sp_MShelpindex stored procedure returns information about name, status, fill factor, index columns names, and file groups for a given table.

Syntax

sp_MShelpindex tablename [, indexname] [, flags]

where

tablename - is the table name. tablename is nvarchar(517).

indexname - is the index name. indexname is nvarchar(258),

with a default of NULL.

flags - flags is int, with a default of NULL.

To get the indexes description for the authors table in the pubs database, run:

USE pubs

EXEC sp_MShelpindex 'authors'

sp_MShelptype

The sp_MShelptype stored procedure returns much useful information about system data types and user data types.

Syntax

sp_MShelptype [typename] [, flags]

where

typename - is the type name. typename is nvarchar(517),

with a default of NULL.

flags - flags is nvarchar(10), with a default of NULL.

To get information about all built-in and user defined data types in the pubs database, run:

USE pubs

EXEC sp_MShelptype

sp_MSindexspace

The sp_MSindexspace stored procedure returns the size in kb, of the indexes found in a particular table.

Syntax

sp_MSindexspace tablename [, index_name]

where

tablename - is the table name. tablename is nvarchar(517).

index_name - is the index name. index_name is nvarchar(258),

with a default of NULL.

To determine the space used by the indexes from the authors table in the pubsdatabase, run:

USE pubs

EXEC sp_MSindexspace 'authors'

sp_MSkilldb

The sp_MSkilldb stored procedure sets a database to suspect mode and uses DBCC DBREPAIR to kill it. You should run this sp from the context of the master database. Use it very carefully.

Syntax

sp_MSkilldb dbname

where

dbname - is the database name. dbname is nvarchar(258).

To kill the pubs database, run:

USE master

EXEC sp_MSkilldb 'pubs'

sp_MStablespace

The sp_MStablespace stored procedure returns the number of rows in a table and the space the table and index use.

Syntax

sp_MStablespace name [, id]

where

name - is the table name. name is nvarchar(517).

id - id is int, with a default of NULL.

To determine the space used by the authors table in the pubs database, run:

USE pubs

EXEC sp_MStablespace 'authors'

Here is the result set from my machine:

Rows DataSpaceUsed IndexSpaceUsed

----------- ------------- --------------

23 8 32

sp_tempdbspace

The sp_tempdbspace stored procedure can be used to get the total size and the space used by the tempdb database. It is used without parameters.

Syntax

sp_tempdbspace

This is the example:

EXEC sp_tempdbspace

Here is the result set from my machine:

database_name database_size spaceused

------------- ----------------------- -----------------------------

tempdb 9.750000 .562500

sp_who2

The sp_who2 stored procedure returns information about current SQL Server 2000 users and processes similar to sp_who, but it provides more detailed information. sp_who2 returns CPUTime, DiskIO, LastBatch and ProgramName in addition to the data provided by sp_who.

Syntax

sp_who [loginame]

where

loginame - the user's login name. If not specified, the procedure

reports on all active users of SQL Server.

This example returns information for the 'sa' login:

EXEC sp_who2 'sa'

5. Source 4 - Useful Undocumented SQL Server 2005 Extended Stored Procedures

http://www.sswug.org/articles/viewarticle.aspx?id=27393

An extended stored procedure (xp) is a dynamic link library that runs directly in the address

space of SQL Server. You can run extended stored procedures as normal stored procedures. Extended

stored procedures are used to extend the capabilities of SQL Server. You can take advantage of

the many extended stored procedures that come with SQL Server, or you can write your own.

In this article, I want to describe some useful undocumented extended stored procedures that

shipped with SQL Server 2005.

sp_MSgetversion

This extended stored procedure can be used to get the current version of Microsoft SQL Server.

To get the current SQL Server version, run:

EXEC master..sp_MSgetversion

Note. A more common way to retrieve the current SQL Server version (this way provides more

information) is to use following SELECT statement:

SELECT @@version

xp_availablemedia

This extended stored procedure returns available drives and free space in bytes on these drives.

In comparison with xp_fixeddrives the xp_availablemedia extended stored procedure returns not

only the hard drives, but all available drives.

The xp_availablemedia returns free space in bytes when xp_fixeddrives returns free space in Mb.

To get the list of all available drives with free space on them, run:

EXEC master..xp_availablemedia

xp_delete_file

This extended stored procedure can be used to delete a SQL Server backup file or a Maintenance

Plan report file.

Syntax:

EXECUTE xp_delete_file 0|1, 'file_name'

0 - to delete a SQL Server backup file.

1 - to delete a Maintenance Plan report file.

For example, to delete the TestBackup.bak SQL Server backup file from the SQL directory of the

D: disk, you can run this statement:

EXEC master..xp_delete_file 0, 'd:\SQL\TestBackup.bak'

xp_dirtree

This extended stored procedure can be used to get a list of all the folders for the folder named

in the xp. To get a list of all the folders in the D:\Install folder, run:

EXEC master..xp_dirtree 'D:\Install'

xp_create_subdir

This extended stored procedure creates a subdirectory for the specified directory. For example,

to create 'SQL' subdirectory in the 'Install' directory on the D: disk, you can run the following:

EXEC master..xp_create_subdir 'D:\Install\SQL'

xp_enum_oledb_providers

This extended stored procedure is used to list of all the available OLE DB providers. It returns

Provider Name, Parse Name and Provider Description. To get a list of all OLE DB providers for your

SQL Server, run:

EXEC master..xp_enum_oledb_providers

xp_enumcodepages

This extended stored procedure can be used to list of all code pages, character sets and their

description for your SQL Server. To get a list of all code pages and character sets, run:

EXEC master..xp_enumcodepages

Note. To run this procedure, you should enable the 'Web Assistant Procedures' using the sp_configure

system stored procedure.

xp_enumerrorlogs

This extended stored procedure returns the list of all error logs with their last change date

and error log files size.

To get the list of error logs, run:

EXEC master..xp_enumerrorlogs

xp_fileexist

You can use this extended stored procedure to determine whether a particular file exists on the

disk or not.

Syntax:

EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]

For example, to check whether the file boot.ini exists on C: disk or not, run:

EXEC master..xp_fileexist 'c:\boot.ini'

xp_fixeddrives

This very useful extended stored procedure returns the list of all hard drives and the amount of

free space in Mb for each hard drive.

To see the list of drives, run:

EXEC master..xp_fixeddrives

xp_getnetname

This extended stored procedure returns the WINS name of the SQL Server that you are connected

to. To view the SQL Server name, run:

EXEC master..xp_getnetname

xp_get_tape_devices

This extended stored procedure is used to get the names of all the available tape devices.

To get a list of all tape devices for your SQL Server, run:

EXEC master..xp_get_tape_devices

xp_readerrorlog

This extended stored procedure returns the content of the last errorlog file. To see the text

of the errorlog file, run:

EXEC master..xp_readerrorlog

xp_regdeletekey

This extended stored procedure can be used to delete an entire key from the registry. You should

use it very carefully.

Syntax:

EXECUTE xp_regdeletekey [@rootkey=]'rootkey',

[@key=]'key'

For example, to delete the 'SOFTWARE\Test' key from 'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regdeletekey

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test'

xp_regdeletevalue

This extended stored procedure can be used to delete a particular value for a key in the registry.

You should use it very carefully.

Syntax:

EXECUTE xp_regdeletevalue [@rootkey=]'rootkey',

[@key=]'key',

[@value_name=]'value_name'

For example, to delete the 'TestValue' value for the 'SOFTWARE\Test' key from

'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regdeletevalue

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test',

@value_name='TestValue'

xp_regread

This extended stored procedure is used to read from the registry.

Syntax:

EXECUTE xp_regread [@rootkey=]'rootkey',

[@key=]'key'

[, [@value_name=]'value_name']

[, [@value=]@value OUTPUT]

For example, to read into the @test variable from the 'TestValue' value from the 'SOFTWARE\Test'

key from the 'HKEY_LOCAL_MACHINE', run:

DECLARE @test varchar(20)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test',

@value_name='TestValue',

@value=@test OUTPUT

SELECT @test

xp_regwrite

This extended stored procedure is used to write to the registry.

Syntax:

EXECUTE xp_regwrite [@rootkey=]'rootkey',

[@key=]'key',

[@value_name=]'value_name',

[@type=]'type',

[@value=]'value'

For example, to write the 'Test' variable to the 'TestValue' value, 'SOFTWARE\Test' key,

'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regwrite

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test',

@value_name='TestValue',

@type='REG_SZ',

@value='Test'

xp_test_mapi_profile

6. Other Hidden Features of SQL Server

(http://stackoverflow.com/questions/121243/hidden-features-of-sql-server)

Snippets

· Returning rows in random order

· All database User Objects by Last Modified Date

· Return Date Only

· Find records which date falls somewhere inside the current week.

· Find records which date occurred last week.

· Returns the date for the beginning of the current week.

· Returns the date for the beginning of last week.

· See the text of a procedure that has been deployed to a server

· Drop all connections to the database

· Table Checksum

· Row Checksum

· Drop all the procedures in a database

· Re-map the login Ids correctly after restore

· Call Stored Procedures from an INSERT statement

· Find Procedures By Keyword

· Drop all the procedures in a database

· Query the transaction log for a database programmatically.

Functions

· HashBytes()

· EncryptByKey

· PIVOT command

Misc

· Connection String extras

· TableDiff.exe

· Triggers for Logon Events (New in Service Pack 2)

· Boosting performance with persisted-computed-columns (pcc).

· DEFAULT_SCHEMA setting in sys.database_principles

· Forced Parameterization

· Vardecimal Storage Format

· Figuring out the most popular queries in seconds

· Scalable Shared Databases

· Table/Stored Procedure Filter feature in SQL Management Studio

· Trace flags

· Number after a GO repeats the batch

· Security using schemas

· Encryption using built in encryption functions, views and base tables with triggers