一些有时候比较有用的扩展存储过程
sp_MSgetversion
This extended stored procedure can be used to get the current version of Microsoft SQL Server.
Syntax:
EXEC master..sp_MSgetversion
SELECT @@version
xp_regaddmultistring
This extended stored procedure is used to create a multi-string registry entry or add a string
to an existing multi-string key. A multi-string registry entry contains multiple string values
in a single registry entry.
Syntax:
EXECUTE xp_regaddmultistring [@rootkey=]’rootkey’,
[@key=]’key’,
[@value_name=]’value_name’,
[@value=]’value’
--For example, to add the ‘Test’ string to the ‘TestValue’ value, in the
--"HKEY_LOCAL_MACHINESoftwareTest" folder, run:
EXECUTE master..xp_regaddmultistring
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARETest’,
@value_name=’TestValue’,
@value=’Test’
xp_regremovemultistring
This extended stored procedure is used to remove a string from a multi-string registry entry.
Syntax:
EXECUTE xp_regremovemultistring [@rootkey=]’rootkey’,
[@key=]’key’,
[@value_name=]’value_name’,
[@value=]’value’
--For example, to remove the ‘Test’ string from the ‘TestValue’ value, in the
--"HKEY_LOCAL_MACHINESoftwareTest" folder, run:
EXECUTE master..xp_regremovemultistring
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARETest’,
@value_name=’TestValue’,
@value=’Test’
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 ‘SOFTWARESQL’ key from ‘HKEY_LOCAL_MACHINE’, run:
EXEC master..xp_regdeletekey
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARESQL’
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 ‘SOFTWARETest’ key from
--‘HKEY_LOCAL_MACHINE’, run:
EXEC master..xp_regdeletevalue
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARETest’,
@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
--"HKEY_LOCAL_MACHINESoftwareTest" folder, run:
DECLARE @test varchar(20)
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARETest’,
@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, in the
--"HKEY_LOCAL_MACHINESoftwareTest" folder, run:
EXEC master..xp_regwrite
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWARETest’,
@value_name=’TestValue’,
@type=’REG_SZ’,
@value=’Test’
xp_regenumkeys
You can use this extended stored procedure to determine what keys are available in a given registry folder.
Syntax:
EXECUTE xp_regenumkeys [@rootkey=]’rootkey’,
[@key=]’key’
--For example, to enumerate the "HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server"
--folder, run:
EXEC master..xp_regenumkeys
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWAREMicrosoftMicrosoft SQL Server’
xp_regenumvalues
You can use this extended stored procedure to display the values associated with a specific key.
Syntax:
EXECUTE xp_regenumvalues [@rootkey=]’rootkey’,
[@key=]’key’
--This example displays the key values under --"HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server120":
EXEC master..xp_regenumvalues
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’SOFTWAREMicrosoftMicrosoft SQL Server120′
xp_enum_oledb_providers
This extended stored procedure can be used to get the list of all available OLE DB providers.
It returns Provider Name, Parse Name and Provider Description.
Syntax:
EXECUTE xp_enum_oledb_providers
--To get a list of all OLE DB providers for your SQL Server, run:
EXEC master..xp_enum_oledb_providers
xp_enumerrorlogs
This extended stored procedure returns the list of all error logs with their last change
date and error log files size.
Syntax:
EXEC master..xp_enumerrorlogs
xp_getnetname
This extended stored procedure returns the WINS name of the SQL Server that you are connected to.
Syntax:
EXEC master..xp_getnetname
xp_readerrorlog
This extended stored procedure returns the content of the last errorlog file.
Syntax:
EXEC master..xp_readerrorlog
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.sys.xp_availablemedia
xp_create_subdir
This extended stored procedure creates a subdirectory for the specified directory. For example,
to create ‘SQL2014’ directory on the C: disk, you can run the following:
EXEC master.sys.xp_create_subdir ‘C:SQL2014’
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 Test.bak SQL Server backup file from the ‘SQL’ directory on the
C: disk, you can run this statement:
EXEC master.sys.xp_delete_file 0, ‘C:SQLTest.bak’
xp_dirtree
This extended stored procedure can be used to get a list of all the subdirectories for
the specified directory. To get a list of all the directories in the ‘Install’ directory
on the C: disk, you can run the following statement:
EXEC master.sys.xp_dirtree ‘C:Install’
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.sys.xp_fileexist ‘c:boot.ini’
xp_fixeddrives
This 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, you can execute the following statement:
EXEC master.sys.xp_fixeddrives
xp_get_tape_devices
This extended stored procedure is used to get the names of all the available tape devices.
Syntax:
EXEC master..xp_get_tape_devices
xp_subdirs
You can use this extended stored procedure to get the list of first level subdirectories
of the specified directory. In comparison with the xp_dirtree extended stored procedure,
the xp_subdirs returns only the first level subdirectories of the specified directory,
not all subdirectories.
For example, to get the list of first level subdirectories of the ‘C:SQL’ directory, you
can run the following:
EXEC master.sys.xp_subdirs ‘C:SQL’