Some SQL Server System stored procedures

--provide list of all database objects

exec sp_help

--provide list of all columns , there datatype and some other important information

sp_help tablename_or_viewname

--Provide defination of given object, object can be function, SP or trigger

sp_helptext view_function_proc_triggername

-- provide list of current running process with some other important information

sp_who2

-- provide list of current running process, provide information less than sp_who2

sp_who

--provide list of tables and views

sp_tables

-- provide list of columnname and some other usefull information of input object

sp_columns table_viewname

-- provide list of all databases with there size

sp_databases

-- Enable you to rename a database or database object

sp_rename

currentname,

newname,

objecttype /*optional*/

--Enable you to set & view the database compatibility value , valid values for sql server 2005 are 60, 65, 70, 80, or 90 /*(90 for sql server 2005)*/

sp_dbcmptlevel

DatabaseName,

Value /*optional*/

--enable you to set values of different database option

sp_dboption dbname, optname, optvalue

--provide you the object dependent and type of dependent object for input object

sp_depends objectname

--enable you to equire a lock on active transaction

sp_getapplock Resource,

lockmode, --can be 'Shared', 'Update', 'Exclusive', 'IntentExclusive'and 'IntentShared'

LockOwner, --can be 'Transaction' and 'Session' -- default is 'Transaction'

LockTimeout, --default null

DbPrincipal -- default public

--provide you the detail of constraint on given objectname

sp_helpconstraint objectname

--

--provide you data and log file info for current database, if u pass a file name to this proc this will give you Db name for that file

sp_helpfile

[filename] --optional

--provide you file group name for current database

sp_helpfilegroup

filegroupname --optional

--provide you list of indexes on given object

sp_helpindex objectname

--provide you list of stats on given object

sp_helpstats objectname

--provide you list of triggers on given object

sp_helptrigger objectname

--provide you users for current Db with Loginname

sp_helpuser

--provide you detail of current locks

sp_lock

spId1, --optional

spId2 --optional

--Provide you overview of server performance

sp_monitor

--provide you list of database on which you have access rights

sp_mshasdbaccess

--provide you list of index on given object and space used by them

sp_msindexspace objectname

http://www.dotnetspider.com/resources/24257-usefull-SQL-Server-System-stored-procedure.aspx

-------------------------------------------

Run following simple script on SQL Server 2005 to retrieve all stored procedure in database:

SELECT * FROM sys.procedures

This will ONLY work with SQL Server 2005.

-------------------------------------------

System Stored Procedures (SQL Server 2000)

http://msdn.microsoft.com/en-us/library/aa259564(SQL.80).aspx

-------------------------------------------

Query that returns list of all Stored Procedures in an MS SQL database

http://stackoverflow.com/questions/219434/query-that-returns-list-of-all-stored-procedures-in-an-ms-sql-database

-------------------------------------------