Sunday, September 13, 2009

Undocumented sps - Part II

As there was a comment on my last post for more extended undocumented SPs, I thought I will do a part - II for it. I haven’t found a serious necessity till date to use the extended sps that have been listed out here.
But anyways, let me put them here as requested.

1) sp_helpextendedproc :

Lists all extended system procedures including the undocumented ones. The system procedures include extended sps which use DLL ( like xp_cmdshell) and other system procedures which are used internally by SQL Server( whose source code is not available for us to see ). So if you are looking for more extended sps than that are listed here then you may very well run sp_helpextendedproc and it may help you to find the one you wanted.

Example :
EXEC master..sp_helpextendedproc

2) xp_getfiledetails:
Provides file details like size( in bytes ), Creation date and time, Last accessed date and time.xp_getfiledetails works only on SQL 2k and it is removed in SQL 2k5.


EXEC master..xp_getfiledetails 'C:\test.txt'

3) xp_subdirs:

Provides the list of subdirectories present in the specified directory.Does the search up to only one level.
EXEC master..xp_subdirs 'C:'

4) xp_dirtree:
Provides the list of subdirectories for the specified directory. Unlike xp_subdirs doesnt limit itself to 1 level and traverses the complete depth.


EXEC master..xp_dirtree 'C:'

5) xp_getnetname:
Returns the Servers network name. Note that it is different from Serverproperty('Servername') as Serverproperty returns the name of the SQLServer running on the Server. But xp_getnetname returns the network name of the machine running the SQLServer.

EXEC master..xp_getnetname

6) xp_enum_oledb_providers :
Provides the list of OLEDB Providers.

7) xp_enumdsn: Used to list all DSNs that are created on SQLServer. Works only with SQL 2000.

8) xp_enumerrorlogs : Lists the number of SQL error log files that are present in SQL Server.

9) xp_enumgroups : Lists the number of Windows groups that are present in the SQL Server Machine. This command lists all the groups present that are present in the machine and not just the ones that have access to SQL Server.

10) xp_fileexist - Can be used to find whether a file/directory exists. Indicates whether its a directory/file and whether it has a parent directory.

There are few more like xp_regdeletekey,xp_regdeletevalue,XP_regread, XP_regWrite to meddle with the registry.I seriously dont expect myself to meddle with the registry and would dump it on the System Admin folks to do that :)
So, I am not getting into the details of it.

1 comment:

Anonymous said...