Undocumented SQL Server 2014 extended stored procedures (Part 2)
In this article, you can find the description of some useful undocumented SQL Server 2014
extended stored procedures.
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:
EXECUTE xp_enumerrorlogs
For example, to get the list of error logs, you can run:
EXEC master..xp_enumerrorlogs
xp_getnetname
This extended stored procedure returns the WINS name of the SQL Server that you are connected to.
Syntax:
EXECUTE xp_getnetname
To view the SQL Server name, you can run:
EXEC master..xp_getnetname
xp_readerrorlog
This extended stored procedure returns the content of the last errorlog file.
Syntax:
EXECUTE xp_readerrorlog
For example, to see the text of the errorlog file, run:
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:
EXECUTE xp_get_tape_devices
To get a list of all tape devices for your SQL Server, run:
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’