Mssql Extended Stored Procedures

MS SQL Server DBCC Commands

1 Prepared By : Ramesh

Extended Stored Procedures These are some documented and undocumented extended stored procedures. These procedures expand the flexibility of T-SQL to allow you to interact with the Windows operating system. Most of the procedures in this section are undocumented and are not supported by Microsoft.

xp_availablemedia The xp_availablemedia extended stored procedure lists the drives that are available to read and write data. To execute the procedure, use the following syntax: master..xp_availablemedia

This outputs the name of the drive, the free space in bytes (shown as low free) and the type of drive. Some of the types of drives you can see in the following results are a floppy drive (1), a hard drive (2), and a writable CD-ROM (8): name -----------------------------A:\ C:\ D:\ F:\

low free ----------884736 1993347072 1982103552 679477248

high free ----------0 0 5 0

media type ---------1 2 2 8

xp_cmdshell The xp_cmdshell extended stored procedure is a commonly used procedure to execute programs using T-SQL. For example, you can use the procedure to execute a DTS package by using the following syntax: master..xp_cmdshell 'DTSRun /S "servername" /U "username" /P "password" /N "DTSPackageName"'

This outputs the following results: output -------------------------------------------------DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. NULL

If you don't want the procedure to produce any output, use the no_output parameter.

xp_dirtree 2 Prepared By : Ramesh

The xp_dirtree extended stored procedure reports all of the subdirectories under the specified root directory. For example, if you'd like to determine which directories exist under the C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP folder, use the following syntax: master..xp_dirtree 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP'

This outputs all the directories under the specified directory as shown here: subdirectory -------------------------------------------------Northwind TranBackups Pubs

depth ----------1 2 1

The depth column shows how deep under the specified directory the directory named in the subdirectory column is. If you don't want the depth column, use the xp_subdirs procedure with the same parameters.

xp_enum_activescriptengines The xp_enum_activescriptengines procedure lists all the scripting languages installed on the server. To execute the procedure, use the following syntax: master..xp_enum_activescriptengines

This outputs the following results, which may vary based on the installed languages: Program ID -----------------------------XML VBScript VBScript.Encode JScript JScript.Encode

Description -----------------------------XML Script Engine VB Script Language VBScript Language Encoding JScript Language JScript Language Encoding

xp_enum_oledb_providers The xp_enum_oledb_providers procedure outputs all the installed OLE DB providers on the server. To execute the procedure, use the following syntax: master..xp_enum_oledb_providers

This results in the following results (mine are abridged): Provider Name -------------------------Microsoft.ISAM.OLEDB.1.1 MSDAORA DTSPackageDSO

Provider Description -----------------------------------------Microsoft ISAM 1.1 OLE DB Provider Microsoft OLE DB Provider for Oracle Microsoft OLE DB Provider for DTS Packages

3 Prepared By : Ramesh

MSOLAP MSDASQL ADsDSOObject MSDAOSP SQLOLEDB Microsoft.Jet.OLEDB.4.0

Microsoft OLE DB Provider for Olap Service Microsoft OLE DB Provider for ODBC Driver OLE DB Provider for Microsoft Directory Microsoft OLE DB Simple Provider Microsoft OLE DB Provider for SQL Server Microsoft Jet 4.0 OLE DB Provider

xp_enumcodepages The xp_enumcodepages procedure lists all the code pages installed on the server. To execute the procedure, use the following syntax: master..xp_enumcodepages

This outputs the following results (mine are abridged): Code Page ----------50932 51932 65001 1258 1252

Character Set ---------------------------------------_autodetect euc-jp utf-8 windows-1258 iso-8859-1

Description ---------------------Japanese (Auto-Select) Japanese (EUC) Unicode (UTF-8) Vietnamese (Windows) Western European

xp_enumdsn The xp_enumdsn procedure outputs a list of DSNs set up on the server. To execute the procedure, use the following syntax: master..xp_enumdsn

This outputs the following results: Data Source Name -------------------------------MS Access Database dBASE Files Excel Files Visual FoxPro Database Visual FoxPro Tables dBase Files - Word FoxPro Files - Word MQIS LocalDB

Description ---------------------------------------Microsoft Access Driver (*.mdb) Microsoft dBase Driver (*.dbf) Microsoft Excel Driver (*.xls) Microsoft Visual FoxPro Driver Microsoft Visual FoxPro Driver Microsoft dBase VFP Driver (*.dbf) Microsoft FoxPro VFP Driver (*.dbf) SQL Server SQL Server

xp_enumerrorlogs The xp_enumerrorlogs procedure outputs a list of the SQL Server error logs on the server, along with their creation dates and sizes. To execute the procedure, use the following syntax: 4 Prepared By : Ramesh

master..xp_enumerrorlogs

This outputs the following results: Archive # ----------0 5 1 2 3 4 6

Date ---------------------------------------03/31/2001 16:29 03/20/2001 07:04 03/30/2001 01:00 03/29/2001 22:11 03/28/2001 01:00 03/22/2001 10:20 03/18/2001 18:26

Log File Size (Byte) -------------------3913 31574 729 13371 3701 2654 205249

xp_enumgroups The xp_enumgroups procedure lists all the Windows local groups on the server. To execute the procedure, use the following syntax: master..xp_enumgroups

If you run this procedure on a server other than Windows NT or 2000, you receive the following error: This system extended procedure is not supported on Windows 95.

If you have Windows NT or 2000, you receive the results similar to the following (mine are limited to 50 characters for the comment): group ----------------------Administrators Backup Operators Guests Power Users Replicator Users

comment -------------------------------------------------Administrators have complete and unrestricted acce Backup Operators can override security restriction Guests have the same access as members of the User Power Users possess most administrative powers wit Supports file replication in a domain Users are prevented from making accidental or inte

xp_fileexist The xp_fileexist procedure determines if a specified file exists in the directory. It can also tell you if the specified variable is a directory. You can use this to trigger certain events. For example, you can use this procedure to test for the existence of a file before launching a DTS package. To execute the procedure to test the existence of a file called autoexec.bat, use the following syntax: master..xp_fileexist 'c:\autoexec.bat'

This outputs the following results (a result of 1 means the file or directory exists): 5 Prepared By : Ramesh

File Exists ----------1

File is a Directory ------------------0

Parent Directory Exists ----------------------1

xp_fixeddrives The xp_fixeddrives procedure displays the fixed drives on the server and how much space in megabytes is available to each drive. You can use this to determine if there is enough space on a drive before creating a new database or launching a DTS package to load large amounts of data. To execute the procedure, use the following syntax: master..xp_fixeddrives

This outputs the following results: drive ----C D E F

MB free ----------1773 1386 12429 29276

xp_getfiledetails The xp_getfiledetails procedure displays information about the specified file, including its size and creation date. To execute the procedure, use the following syntax: master..xp_getfiledetails 'c:\autoexec.bat'

This outputs the following results: Alternate Name Size Creation Date Creation Time Last Written Date --------------- ------ ------------- ------------- ----------------(null) 221 19800101 0 19970327 Last Written Time Last Accessed Date Last Accessed Time Attributes ----------------- ------------------ ------------------ ----------134752 19991007 0 128

xp_get_MAPI_profiles The xp_get_MAPI_profiles procedure will return a list of MAPI profiles that are available to SQL Mail. The procedure executes with no parameters: master..xp_get_MAPI_profiles

If profiles exist, you will receive results that look something like this: 6 Prepared By : Ramesh

Profile name Is default profile ---------------------------------- -----------------Microsoft Outlook Internet Setting 1

If there are no MAPI profiles installed, you will receive the following message: L -28 Server: Msg 18030, Level 16, State 1, Line 0 xp_get_mapi_profiles: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

Note There is a sister procedure to xp_get_MAPI_profiles called

xp_get_MAPI_default_profile that will return the default MAPI mail profile only.

xp_getnetname The xp_getnetname procedure displays the computer name of the SQL Server you're connected to. To execute the procedure, use the following syntax: master..xp_getnetname

This outputs the following results: Server Net Name --------------XANADU

xp_loginconfig The xp_loginconfig procedure lists the security and login configuration for SQL Server. To utilize the procedure, use the following syntax: master..xp_loginconfig

This outputs the following results: name ---------------------------login mode default login default domain audit level set hostname map _ map $ map #

config_value ---------------------------Mixed guest BEDROCK none false domain separator NULL -

7 Prepared By : Ramesh

You can also optionally list the individual configuration item if you only want selective information: master..xp_loginconfig 'login mode'

xp_logevent The xp_logevent procedure is a useful procedure that logs events to Event Viewer from T-SQL. To use the procedure, you have to provide an error number, followed by the error message, and finally the severity. Available severities are INFORMATIONAL, WARNING, and ERROR. User-defined error numbers begin at 50,001. For example, you can use the following syntax to log an event into Event Viewer: master..xp_logevent 50001, 'Bad login occured', warning

xp_logininfo The xp_logininfo extended stored procedure produces valuable information about which Windows users have rights to your SQL Server and the types of permissions they have. To execute the procedure, use the following base syntax: master..xp_logininfo

This results in the following information: account name ----------------------BUILTIN\Administrators XANADU\bknight

type -------group user

privilege --------admin user

mapped login name -----------------------BUILTIN\Administrators XANADU\bknight

You can also gain information about individual Windows users or groups by specifying an individual user or group as a parameter: master..xp_logininfo 'BUILTIN\Administrators'

xp_ntsec_enumdomains The xp_ntsec_enumdomains procedure lists the domain that your Windows server is a member of. To execute the procedure, use the following syntax: master..xp_ntsec_enumdomains

This outputs the following: Domain --------------------------------XANADU

8 Prepared By : Ramesh

xp_readerrorlog The xp_readerrorlog procedure returns the contents of the current error log. To execute the procedure, use the following syntax: master..xp_readerrorlog

You can also pass the extended stored procedure a simple parameter of what error log you'd like to read. This number corresponds with the filename. For example, to read the file errorlog.5, you would use the following command: master..xp_readerrorlog 5

xp_regdeletekey The xp_regdeletekey procedure allows you to delete a key from your registry. Be especially careful when using this procedure as it will not warn you before the procedure deletes the entire key. To run the procedure, use the @rootkey parameter to specify the root registry key and then the @key parameter to designate the key you want to delete: master..xp_regdeletekey @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NewKey'

This outputs the following error if the registry key can't be found: Msg 22001, Level 1, State 22001 RegDeleteKey() returned error 2, 'The system cannot find the file specified.'

If the key can be found, you receive no message.

xp_regdeletevalue You can also delete individual data items inside a registry key by using the xp_regdeletevalue procedure. The procedure uses the same parameters as the xp_regdeletekey procedure, but has an added @value_name parameter, which is the data item name: master..xp_regdeletevalue @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NewKey', @value_name='NewKeyName'

xp_regenumvalues The xp_regenumvalues lists all the registry data items and values in a given key. To use the procedure, use the following syntax: master..xp_regenumvalues 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion\'

9 Prepared By : Ramesh

This results in the following: Value -----------------------------RegisteredOwner Value -----------------------------SerialNumber Value -----------------------------CurrentVersion Value -----------------------------Language

Value Data ------------------------------ -----MSEmployee NULL Data -----------2082537408 Data -----------------------------8.00.194 Data ----------1033

xp_regread The xp_regread procedure reads an individual registry key to determine its existence or to read a data item in the key. The @rootkey parameter is the root key in the registry, and the @key parameter is the individual key. To use the procedure, you must specify the registry key's root and key as shown here: master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\'

By using these two variables, you are only testing the existence of the key, which results in the following: KeyExist ----------1

You can also read individual data items inside the key by using the added @value_name parameter, which represents the name of the data item: master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\',@value_name='defaultLogin'

This results in the following: Value Data ------------------------------ -----------------------------defaultLogin guest

xp_regwrite The xp_regwrite procedure allows you to create a data item in your server's registry and optionally create a new key. To use the procedure, you must specify the root key with the @rootkey parameter and designate an individual key with the @key parameter. If the key doesn't 10 Prepared By : Ramesh

exist, it is created. The @value_name parameter designates the data item and the @type parameter the type of the data item. Valid data item types include REG_SZ and REG_DWORD. The final option is the @value parameter, which assigns a value to the data item. The following syntax adds a new key called NewKey, and creates a new data item under it called NewKeyName: master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NewKey', @value_name='NewKeyName', @type='REG_SZ', @value='Test'

xp_subdirs The xp_subdirs procedure displays all the subdirectories one level down from the specified directory. To execute the procedure, simply pass it the root directory you'd like to see: master..xp_subdirs 'C:\Program Files\Microsoft SQL Server\'

This will output results similar to the following: subdirectory -----------------------80 MSSQL

If you specified an invalid directory, the following results will be displayed: Server: Msg 22006, Level 16, State 1, Line 0 Error executing xp_subdirs: FindFirstFile failed!

11 Prepared By : Ramesh