Useful MS-SQL Server System Stored Procedures

In this article you will learn useful MS-SQL Server System Stored Procedures.

I am using the Adventure Works2008 database for this article. The Stored Procedures described here are very useful to speed up your work.

1. sp_help 'TableName'

-- returns the entire table structure.

For example:

  1. EXEC sp_help 'Production.Product'

table structure

2. sp_spaceused 'TableName'

-- returns the memory related details of the table.

For example:

  1. EXEC sp_spaceused 'Production.Product'

memory related details

3. sp_helpconstraint 'TableName'

-- returns details of various constraints used in the table.

For example:

  1. exec sp_helpconstraint 'Production.Product'

details of different constraints

4. sp_helpindex 'TableName'

-- To get the Index details of a table.

For example:

  1. exec sp_helpindex 'Production.Product'

get the Index Details

5. sp_depends 'TableName'

--returns the list of Stored Procedures in which the table is used.

For example:

  1. exec sp_depends 'Production.Product'

list of stored procedures

6. sp_depends 'StoredProcedureName'

-- returns the list of tables that are used in the current Stored Procedure.

For example:  
  1. exec sp_depends 'dbo.ufnGetProductListPrice'

list of tables

7. sp_helptext 'StoredProcedureName'

Returns the strored procedure definition.

For example:

  1. EXEC sp_helptext 'dbo.ufnGetProductListPrice'

strored procedure defination