Extended stored procedures (Un-Documented)

xp stands for extended stored procedure which is DLL, programmed using the SQL Server Open Data Services API. These extend the capabilities of SQL Server.

sp_MSgetversion

To get the current version of MSSQL Server.

EXEC master..sp_MSgetversion

Alternative way to retrieve version related detailed information.

SELECT @@version

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

xp_dirtree

To get a list of all the folders.

To get a list of all the folders in the C:\WINDOWS folder, run:

EXEC master..xp_dirtree 'C:\WINDOWS'

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

xp_enum_oledb_providers

This 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_enumerrorlogs

This  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 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. Run following example:

EXEC master..xp_fileexist D:\DSC01568.JPG'

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

xp_fixeddrives

This  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 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. To see the text of the errorlog file, run:

EXEC master..xp_readerrorlog

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

xp_regdeletekey

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

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

e.g.  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 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'

e.g.  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 is used to read from the registry.

Syntax:

EXECUTE xp_regread [@rootkey=]'rootkey',

                   [@key=]'key'

                   [, [@value_name=]'value_name']

                   [, [@value=]@value OUTPUT]

e.g., 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  is used to write to the registry.

Syntax:

EXECUTE xp_regwrite [@rootkey=]'rootkey',

                    [@key=]'key',

                    [@value_name=]'value_name',

                    [@type=]'type',

                    [@value=]'value'

e.g. 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 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 'D:\MULTIMEDIA'

Note: These extended stored procedure tested in MSSQL-2012 but it’s not standard and supported by Microsoft, can be discontinue in upcoming version of MSSQL.