Some undisclosed extended stored procedures in SQL Server

[Post] www.inbaidu.com

Some undisclosed extended stored procedures in SQL Server

The extended storage process (xp) is a Dynamic Linked Library that runs directly in the SQL Server address space. It is developed by using the SQL Server Open Data Services API. We can run the extended stored procedure in the "query analyzer" just like executing a common stored procedure. Extended stored procedures are used for SQL Server's extended functions. We can directly use the numerous extended stored procedures attached to SQL Server, you can also use programming languages such as C or C ++ to compile your own extended stored procedures.
In this article, I will tell you some undisclosed useful extended stored procedures. These stored procedures can run on SQL Server 7.0, of course, SQL Server 2000.

1. sp_MSgetversion
This extended stored procedure can be used to obtain the current version of Microsoft SQL Server. To obtain the SQL Server version, run:
EXEC master .. sp_MSgetversion
Note that there is also a way to get the current version of SQL Server (this method can get more information), that is, use the following SELECT statement:
SELECT @ version

2. xp_dirtree
This extended stored procedure can be used to list all folders named in extended storage (xp. To list all the folders in C: \ MSSQL7, run:
EXEC master .. xp_dirtree 'C: \ mssql7'

3. xp_enum_oledb_providers
This extended stored procedure is used to list all available ole db providers ). It returns the provider Name, resolution Name (Parse Name), and provider description. To obtain all the ole db providers of your SQL Server, run:
EXEC master .. xp_enum_oledb_providers

4. xp_enumcodepages
This extended stored procedure can be used to list all your SQL Server code pages, character sets, and their descriptions. To view the information, run:
EXEC master .. xp_enumcodepages

5. xp_enumdsn
This extended stored procedure system all DSN and their descriptions. To view the system DSN, run:
EXEC master .. xp_enumdsn

6. xp_enumerrorlogs
This extended stored procedure returns a list Of all error logs and their last change dates. To obtain the error log list, run:
EXEC master .. xp_enumerrorlogs

7. xp_enumgroups
This extended stored procedure returns the list of Windows NT groups and their descriptions. To obtain a list of Windows NT groups, run:
EXEC master .. xp_enumgroups

8. xp_fileexist
We can use this extended stored procedure to determine whether a specific file exists on the disk. Usage:
EXECUTE xp_fileexist filename [, file_exists int output]
For example, to check whether the C: disk contains the boot. ini file, run:
EXEC master.. xp_fileexist 'C: \ boot. ini'

9. xp_fixeddrives
This is a very useful extended storage process. It returns a list Of all hard disks and their free space in MB. To view the drive list, run:
EXEC master .. xp_fixeddrives

10. xp_getnetname
This extended stored procedure returns the WINS name of the SQL Server you are connected. View the name and run the following command:
EXEC master .. xp_getnetname

11. xp_readerrorlog
This extended stored procedure returns the error log Content. You can find the error Log in the default directory C: \ MSSQL7 \ Log of SQL Server 7.0. To view the error log, run:
EXEC master .. xp_readerrorlog

12. xp_regdeletekey
This extended stored procedure will delete a primary key from the registry. Be careful when using this function! Usage:
EXECUTE xp_regdeletekey [@ rootkey =] 'rootkey ',
[@ Key =] 'key'
For example, to delete the primary key "SOFTWARE \ Test" in "HKEY_LOCAL_MACHINE", run:
EXEC master .. xp_regdeletekey @ rootkey = 'HKEY _ LOCAL_MACHINE ',
@ Key = 'Software \ Test'

13. xp_regdeletevalue
This extended stored procedure will delete a specific key value in the registry. Be careful when using this function! Usage:
EXECUTE xp_regdeletevalue [@ rootkey =] 'rootkey ',
[@ Key =] 'key ',
[@ Value_name =] 'value _ name'
For example, delete "Testvalue" under "SOFTWARE \ Test" from "HKEY_LOCAL_MACHINE" and execute:
EXEC master .. xp_regdeletevalue @ rootkey = 'HKEY _ LOCAL_MACHINE ',
@ Key = 'Software \ test ',
@ Value_name = 'testvalue'

14. xp_regread
This extended stored procedure updates data from the registry. Usage:
EXECUTE xp_regread [@ rootkey =] 'rootkey ',
[@ Key =] 'key'
[, [@ Value_name =] 'value _ name']
[, [@ Value =] @ value OUTPUT]
For example, to read "Testvalue" under "SOFTWARE \ Test" in "HKEY_LOCAL_MACHINE" to the variable @ test, run:
DECLARE @ test varchar (20)
EXEC master .. xp_regread @ rootkey = 'HKEY _ LOCAL_MACHINE ',
@ Key = 'Software \ test ',
@ Value_name = 'testvalue ',
@ Value = @ test OUTPUT
SELECT @ test

15. xp_regwrite
This extended stored procedure is used to write the registry. Usage:
EXECUTE xp_regwrite [@ rootkey =] 'rootkey ',
[@ Key =] 'key ',
[@ Value_name =] 'value _ name ',
[@ Type =] 'type ',
[@ Value =] 'value'
For example, write the variable "Test" to "Testvalue" under "SOFTWARE \ Test" in "HKEY_LOCAL_MACHINE" and execute:
EXEC master .. xp_regwrite
@ Rootkey = 'HKEY _ LOCAL_MACHINE ',
@ Key = 'Software \ test ',
@ Value_name = 'testvalue ',
@ Type = 'reg _ SZ ',
@ Value = 'test'

16. xp_subdirs
This extended stored procedure is used to list folders named in extended storage (xp. Compared with xp_dirtree, xp_subdirs only returns folders with a depth of 1 (depth = 1. Here is an example:
EXEC master .. xp_subdirs 'C: \ mssql7'

Note: Remember that these undisclosed extended stored procedures are not officially supported by Microsoft and may not be found in the next version of SQL Server.