Extended stored procedure Essays in SQL Server (several useful procedure small summaries)

server| Stored Procedures
Extending stored procedures in SQL Server is not a lot of opportunities to use directly

I summarize the ways in which I know how to use some of the useful extended stored procedures as follows:









--Get MS SQL version number

Execute master.. Sp_msgetversion

Go

Character_value

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

8.00.760 1 3





(The number of rows affected is 1 rows)









--Get Hard disk file information

--Parameter Description: directory name, directory depth, whether to display files

Execute master.. Xp_dirtree ' C: '

Go

Execute master.. Xp_dirtree ' C: ', 1

Go

Execute master.. Xp_dirtree ' C: ', 1,1

Go









--Lists all OLE DB-provided programs installed on the server

Execute master.. Xp_enum_oledb_providers

Go









--Lists all code pages installed on the server

Execute master.. Xp_enumcodepages

Go









--Lists the DSN configured on the server

Execute master.. Xp_enumdsn

Go









--Lists the SQL Server error log list, and then updates the time

Execute master.. Xp_enumerrorlogs

Go













--List all Windows local groups on the server

Execute master.. Xp_enumgroups

Go









--Detection of file existence

Execute master.. Xp_fileexist ' C:\a.bak '

Go





DECLARE @flag int

EXEC master.. Xp_fileexist ' C:\abc.bak ', @flag out

If @flag =1

Begin

print ' exist '

End

Else

Begin

print ' no exist '

End

Go













--Lists the fixed drives on the server and the free space for each drive

Execute master.. Xp_fixeddrives

Go









--Gets the computer name of the current SQL Server server

Execute master.. Xp_getnetname

Go













--List the details of the current error log

EXEC [Master]. [dbo]. [Xp_readerrorlog]

Go









--Lists all the next subdirectories of the specified directory

EXEC [Master]. [dbo]. [Xp_subdirs] ' C:\WINNT '

Go









---List the name of the drive

--Free space in bytes (low)

--Drive Type: Floppy (1), Hard drive (2), CD-ROM (8)

EXEC [Master]. [dbo]. [Xp_availablemedia]

Go





--The effect is as follows:

Name low free high free media type

C:\ 1270386688 0 2

D:\ 1726824448 2 2

E:\ 875053056 10 2

F:\ 0 0 8





(The number of rows affected is 4 rows)









There are also in [master]. [dbo]. [sp_addlogin] Inside has the encryption function Pwdencrypt, everybody interested may try





I hope the knowledge mentioned above will be a clue to you.

Of course, welcome communication and correction.





Author:aierong

Blog:http://www.cnblogs.com/aierong

Email:aierong@126.com