System stored procedures - Beginner Level

Introduction

One of the projects I worked had almost 1000+ SQL Server database tables and 10000+ stored procedures. This project was originally developed by our client and later on support work was given to us. Most of the business logic was written in TSQL and front end developed in vb, asp and vb.Net and there were no proper documentation available. In most of the situation we had to do an impact analysis for the data base changes. Adding one parameter in one stored procedure will impact many other stored procedure since these procedures are called from other procedures. Most of the time calling state ment was embedded in the strings. To identify the impacted procedure sys_depends (which is system stored procedure used to identify the dependency) is not reliable since most of the time it's failed to give reliable result. But we found that sql server stored the entire database informations in the system tables. With system table literally you can query anything related to your database.

SQL server database comes with many sotred procedures which will aid you in managing the sql server objects.This article will give you a brief introduction on various system stored procedures and explain how you can write sql queries instead of using the system procedures.

Advantage

Writting your own SQL Query will give you more control over the result .If you know your system tables you can manage any situation. You don't have to remember hundreds of stored procedures to get your work done. I listed few examples here, you can just explore the system tables to get more information.

In a sql server database you can find the system stored procedure under the master database. Normally the names begins with sp_<procedure name> ( it is a good practice not to begin your stored procedure with sp_ ).

The best source of information for sql system table is the microsoft sys table maps. you can down load the map using the following link .this interactive map will give you drill down experience to have a better understanding on sql server system tables and views

Click here to Download Microsoft System Table Map for sql server 2000

Click here to Download sql server 2005 system view maps

Most Commonly Used system Stored Procedures

Sp_help <Object Name>

Sp_help will give you the schema of the table or the parameter list of the stored procedure . This stored procedure is very helpful when you are working In sql query window (ALT + F1 is defined as short cut key for this stored procedure).

The following query will give you the same result.

Select 
A.NAME,B.Name,A.length,A.iscomputed 
From 
Syscolumns A,Systypes B
where id = 
(select id from sysobjects where name='<sql server object name>')
and A.XUSERTYPE=B.XUSERTYPE

In the above query i have used three tables. Sysobjects, Syscolumns and Systypes. Based on your need you can customize the query. It just goes to the sysobjects tables to find the object id, sql server stores the object (object are stored procedures, tables etc) related information in this table. Syscolumns is the child table of sysobjects and keeps the fields, paratmeter information ( in case of stored Procedures) etc. ID field is the foreign key to syscolumn table. And systype is master table for keeping all the data type used in the sql server.

When ever you create a table an entry will be made to sysobjects and each time you add a fields to the table. information will go to syscolumns.

Sp_helptext <Object Name>

This is a very useful stored procedure especially when you work in the sql query window (sql analyzer for sql 2000 user). This will give you the stored procedure text

Select text from syscomments 
Where id =(select id from sysobjects where name='<sql server object name>')

The above query will give you the same result as sp_helptext. You may need to format the text if you are running this in sql query window. But this can be very useful if you are searching whole data base stored procedure for some inline comments or some common text. In our organization when ever some body makes the changes to a stored procedure we will put the inline documentation like who changed, change description etc. You can query these details in the text columns to find the details.

Sp_depends <Object Name>

If you are working in large database, before changing a table or a stored procedure you may need to know about the tables, stored procedures and functions impacted by your changes sp_depends will help you in this regard ,to get the dependent object details, it gives the information like where are all the table is referred

select distinct 
B.name 
From syscomments A inner join sysobjects B 
on A.id = B.id 
where text like '<sql server object name>'

The above query give you a better result, it just uses syscomments and sysobjects tables you can explore more in to this.

This article is just a starting point .if you are exploring the sys tables it will really speed up your task. You can do anything you want do with the sql. Sql is shipped with many system stored procedures but if you really know the system tables you don't have to remember many of that .instead you can just play around with the system tables.

Don't forget to vote for this article. Your suggestion will help me to improve this article and excuse on my English language i know that it is not up to the mark.