SQL Server Stored Procedures and parameter examples

Some are used in the enterprise management GUI of SQL 2000 and are not intended for other processes. Microsoft has estimated that some of these stored procedures will be deleted (or deleted) from future SQL Server versions ). Although these stored procedures may be useful and save you a lot of time, they can change their functions or simply delete them at any time.

The following chart shows that new stored procedures are introduced when many stored procedures are migrated from one Microsoft SQL Server version to another, while some of the original stored procedures are deleted from the installation package. Most stored procedures, if not all, require the user to be a system administrator server role to execute these stored procedures. The stored procedure that interacts with the file system also requires that the user executing the stored procedure (and the SQL Server service account) have the permission to access the file/folder.

Stored Procedure name	Sqls 2000	Sqls 2005	Sqls 2008
Sp_executeresultset	X
Sp_msforeachdb	X	X	X
Sp_msforeachtable	X	X	X
Sp_readerrorlog	X	X	X
Xp_create_subdir		X	X
Xp_delete_file		X	X
Xp_dirtree	X	X	X
Xp_fileexist	X	X	X
Xp_fixeddrives	X	X	X
Xp_getfiledetails	X
Xp_getnetname	X	X	X
Xp_loginconfig	X	X	X
Xp_makecab	X
Xp_msver	X	X	X
Xp_get_mapi_profiles	X	X	X
Xp_subdirs	X	X	X
Xp_test_mapi_profile	X	X	X
Xp_unpackcab	X

Sp_executeresultset

Microsoft deleted the sp_executeresultset in SQL Server 2005.Program. It allows you to use select queries to generate dynamic SQL statements when you are idleCode. Then, the SQL command as the result will be executed on the database. It allows you to create a separate line of code. This line of code can query the number of records of each table in your database in a single step (as shown in the example ). This is an undisclosed stored procedure and you cannot know why it was deleted. However, alas, this convenient and useful stored procedure is no longer available.

Exec sp_execresultset 'select' select ''' + name + '''''', Count (*) from ''+ name From sysobjects Where xtype = ''u ''' Sp_msforeachdb/sp_msforeachtable

 

Sp_msforeachdb/sp_msforeachtable

Sp_msforeachdb and sp_msforeachtable encapsulate a pointer. They allow you to execute T-SQL code for each database on your SQL Server and each table in the current database separately. However, you cannot use the sp_msforeachtable command in a sp_msforeachdb command in SQL2000 or earlier versions. The pointer names used in these stored procedures are the same (hcforeach). Therefore, an error is returned every time sp_msforeachtable is executed, indicating that the pointer name is already in use. In SQL Server 2005, Microsoft solved this problem. To execute the "Next" command, you must tell one of the stored procedures that it will use a different replacement character instead of the default question mark. I changed the replacement character in the database command because it is simpler.

Print the name of each table in the current database

Exec sp_msforeachtable 'print ''? '''

Print every database on the current server

Exec sp_msforeachdb 'print ''? '''

Print every table in the current database

Exec sp_msforeachdb 'use [@] exec sp_msforeachtable ''print

''''@.? ''''''','@'

Sp_readerrorlog/xp_readerrorlog

The storage process sp_readerrorlog actually has two forms. Each item has the same work content. One is a simple encapsulation of the other. The encapsulated stored procedure is sp_readerrorlog, which calls xp_readerrorlog. Both have four input parameters, but only the first two are useful to us. The first parameter sets the number of the file you want to see. The second is the log to be viewed (the errorlog is 1 or empty, and the SQL Agent log is 2 ). This allows you to quickly and easily view your error logs, rather than viewing the bloated log collectors caused by SQL Server 2005 and SQL 2008.

View the current SQL errorlog file.

Exec sp_readerrorlog

Exec sp_readerrorlog 0, 1

View the preceding SQL Agent log file.

Exec sp_readerrorlog 1, 2

Xp_create_subdir

The xp_create_subdir stored procedure introduced in SQL Server 2005 is very lightweight because you can use it to create folders on the SQL server hard disk or from a network share within the T-SQL.

Exec xp_create_subdir 'C:/MSSQL/data'

Xp_delete_file

Use the xp_delete_file stored procedure introduced in SQL Server 2005 to delete files from the hard disk of SQL Server or from a network share within the T-SQL.

Xp_dirtree

The xp_dirtree stored procedure allows you to view the folder tree structure and/or a list of files in a folder. This stored procedure has several parameters to control the depth of the stored procedure query, whether to return files and folders, or to only return folders. The first parameter sets the folder to be viewed. (It is recommended that you do not execute this stored procedure in the root directory of the Windows system because it takes some time to generate the tree and return data .) The second parameter limits the recursive level of the stored procedure. The default value is zero or all levels. The third parameter indicates that the Stored Procedure contains files. The default value is zero or only for the folder. Value 1 indicates the file that contains the result set. Defining a value not zero for the third parameter will add a line to the output call file, which is a small file that is displayed in a folder or file.

Obtain the complete directory tree.

Exec xp_dirtree 'd:/MSSQL /'

Obtain the first two levels of the directory tree.

Exec xp_dirtree 'd:/MSSQL/', 2

Obtain the first three levels of directories, including files.

Exec xp_dirtree 'd:/MSSQL/', 3, 1

Exec xp_dirtree 'd:/MSSQL /'

Xp_fileexist

This SQL Server Stored Procedure, xp_fileexist, is used to determine whether a file exists on a hard disk or on a network share. It is very useful for the stored procedure of downloading data from the rule document. It allows you to check whether a file exists before you attempt to download data blindly. This stored procedure has two parameters. Use the first parameter to determine whether the desired file or folder exists. The second parameter is an output parameter. If it is defined, 1 or 0 is returned Based on the existence or absence of the file.

This parameter is not available:

Exec xp_fileexist 'C:/importfile.csv'

This parameter is available:

Declare @ file_exists int

Exec xp_fileexist 'C:/importfile.csv ', @ file_exists output

Select @ file_exists

Xp_fixeddrives

Xp_fixeddrives is one of the most useful stored procedures. It shows all the drive names in a list and the free space on each drive. This parameter has a separate optional input parameter that can filter results by drive type. If set to value 3, all large storage devices (CD-ROM, DVD, etc.) will be returned; if set to value 4, the hard drive will be returned; when set to 2, a portable device (USB plugging drive, flash drive, and so on) will be returned ).

Returns all the drives.

Exec xp_fixeddrives

Return to hard drive only

Exec xp_fixeddrives 2

Xp_getfiledetails

Xp_getfiledetails is another very useful stored procedure that was last available in SQL Server 2000. This stored procedure returns information about the size, date, and attribute of the specified file, including the date and times of creation, access, and modification.

Exec xp_getfiledetails 'C:/filetoload.csv'

Xp_getnetname

The stored procedure xp_getnetname returns the name of the physical machine on which Microsoft SQL Server is installed. You can make the machine name returned as a dataset or as a variable.

This parameter is not available

Exec xp_getnetname

Use this parameter

Declare @ machinename sysname

Exec xp_getnetname @ machinename output

Select @ machinename

Xp_loginconfig

The SQL Server Stored Procedure will tell you some basic verification information about the users who execute it. It tells you the verification method (Windows or SQL logon), the default domain of the server, the audit level, and some internal separator information.

Exec xp_loginconfig

Xp_makecab

In SQL Server 2000, Microsoft gave us the ability to directly compress system files from the T-SQL without having to go to DOS through xp_mongoshell and run third-party software like PKZIP or WinZip. This command is xp_makecab. It allows you to specify the files you want to compress and the cab files you want to compress. You can select the ZIP file, which is in the. ZIP file format or not compressed. The first parameter shows the path to the cab file. This is where you want to create and add files. The second parameter is the compression level. If you want to use a detailed log record, use the third parameter. The fourth parameter is followed by the name of the file you want to compress. In my tests, I can upload 45 file names to be compressed during the extended storage process, which means it is a flexible solution for your data compression requirements.

Exec xp_makecab 'C:/test. cab', 'mszip ', 1, 'c:/test.txt', 'c:/test1.txt'

Xp_msver

Stored Procedure xp_msver is very useful when viewing system information. It returns a large amount of information about the host operating system-SQL version number, language, CPU type, copyright and trademark information, Microsoft Windows version, CPU count and affinity settings, physical memory settings, and your products. key. This stored procedure has many input parameters that allow you to filter the returned records. Each parameter is a sysname data type, which accepts the name of a record. If all parameters are specified, only the specified row is returned as the parameter.

No filtering is specified.

Exec xp_msver

Only the platform and comments records are returned.

Exec xp_msver 'Platform ', 'comments'

Xp_get_mapi_profiles

The xp_get_mapi_profiles stored procedure helps you configure SQL mail. During execution, it calls windows through the SQL mail component of SQL Server, and displays the available mapi summary list configured in outlook, and specifies which summary is the default summary. If it does not display any records, or outlook is not correctly configured, or outlook summary is configured, but SQL server is not running under a domain account. To use this stored procedure in SQL Server 2005 or SQL Server 2008, you must use the surface area configuration (peripheral application configurator) in the tool or sp_configure stored procedure, set the "SQL mail XPS" option to available.

Exec xp_get_mapi_profiles

Xp_subdirs

The xp_subdirs stored procedure displays a subset of information available through xp_dirtree. Xp_subdirs will display all subfolders in the given folder. It is convenient when you dynamically construct a directory tree in a table, and you do not need to worry about other parameters of the xp_dirtree stored procedure.

Exec xp_subdirs 'd:/MSSQL'

Xp_test_mapi_profiles

Xp_test_mapi_profiles is another undisclosed stored procedure, which is useful when you create SQL mail. It starts and stops an mapi session to ensure that the mapi configuration is correct and works within Microsoft SQL Server. I should point out that it does not verify the mail server configuration in the mapi client (outlook) or send test information.

The stored procedure accepts a separate input parameter. This parameter is the name of the mapi summary you want to test. Just like the xp_get_mapi_profiles stored procedure, this stored procedure must run in SQL Server 2005 and SQL Server 2008. You must configure the configuration in the surface area (peripheral application configurator) in the tool or sp_configure stored procedure, set the "SQL mail XPS" option to available.

When using the SQL mail stored procedure, make sure that SQL mail is still expected to be deleted from the Microsoft SQL server platform. This means that the sp_get_mapi_profiles and xp_test_mapi_profiles stored procedures are expected to be deleted because they are part of the SQL mail subsystem. You should do all the mail work in SQL Server 2005, and then use database mail instead of SQL mail to ensure that the code can be transplanted to the future version of SQL Server. Microsoft originally planned to delete SQL mail in SQL Server 2008, but it is still unknown whether it will exist in SQL Server 2008 in the future based on its inclusion in the current test version.

Xp_unpackcab

The xp_unpackcab extended stored procedure comes with the xp_makecab stored procedure. It can do the same thing as its name: Extract files from the cab file. The first parameter is the cab file, the second parameter is the path you want to extract, and the third parameter is the detailed log. The fourth parameter allows you to specify the name of the extracted file.

Exec xp_unpackcab 'C:/test. cab', 'c:/temp/', 1

Although it is not intended to list the complete list of undisclosed stored procedures in SQL Server, however, it is indeed expected to make the SQL server administrator's work easier to provide a reference for many undisclosed stored procedures. Remember, you should never expect that these stored procedures will still exist from one SQL Server version to another, and you should not expect that their Code bases will remain the same between different versions. It means to encode and enjoy the convenience it brings.

All information about Microsoft SQL Server 2008 (Katmai) is based on the software's beta version 10.0.1019.