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.
