Usefull SQL Server System stored procedures

Some helpful System Stored Procedures are listed here for the use of members.

--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

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

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

-- provide list of all databases with there size

-- Enable you to rename a database or database object

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)*/

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
[filename] --optional

--provide you file group name for current database
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

--provide you detail of current locks
spId1, --optional
spId2 --optional

--Provide you overview of server performance

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

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


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: