--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
-------------------------------------------
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)
-------------------------------------------
Query that returns list of all Stored Procedures in an MS SQL database
-------------------------------------------