Using System Stored Procedures

Whenever you add a database, add a login, create a table or modify any object, the changes reflect in the system tables. System tables are used by the SQL server’s for its own reference but we can also view their contents. But the information stored in them is somewhat in binary or unreadable format, therefore Microsoft has given us System stored procedures (almost 650) to read or modify the data in them.
All system stored procedures are stored in master and msdb databases and normally begin with ‘sp_’

Some of the common System Stored Procedures are

Sp_tables: To show all tables for a particular database
Sp_stored_procedures: To show all stored procedures for a particular database
Sp_database: List the entire databases on the server
Sp_password: Used to change passwords for standard logins.

Using Extended Stored Procedures

The do just what their name implies: They extend the functionalities of SQL Server so that it can do things which normally a database server doesn’t expect to do. Fro example you will not expect a database server to check for file existence, show the files in a particular directory etc. Their name starts with “xp_”

I am giving you two examples of extended stored procedures.

xp_cmdshell: This stored procedure is used to run commands which are usually run from command prompt such as dir command.

The example below will display the directory of files in drive C:

USE Master
Exec xp_cmdshell ‘dir c:’

xp_fileexist: This stored procedure is used to test the existence of the specified file. It returns ‘1’ if file exists otherwise 0.

USE Master
Exec xp_fileexist ‘c:\temp.txt’

The above query check whether the file “temp.txt’ resides in C:. Here is the result,

I think the topic should now be concluded. Stored Procedures are very powerful tool in SQL server to enhance performance and security, make database management easier and for administrative tasks.