Sql Server System Stored Procedures

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


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


SQL Server Extended Stored Procedures


p_MSgetversion
This extended stored procedure can be used to get the current version of Microsoft SQL Server. To get the current SQL Server version, run:

EXEC master..sp_MSgetversion

Note. A more common way to retrieve the current SQL Server version (this way provides more information) is to use following SELECT statement:

SELECT @@version



xp_dirtree
This extended stored procedure can be used to get a list of all the folders for the folder named in the xp. To get a list of all the folders in the C:\MSSQL7 folder, run:

EXEC master..xp_dirtree 'C:\MSSQL7'


xp_subdirs
This extended stored procedure is used to get the list of folders for the folder named in the xp. In comparison with xp_dirtree, xp_subdirs returns only those directories whose depth = 1.

This is the example:

EXEC master..xp_subdirs 'C:\MSSQL7'




xp_enum_oledb_providers
This extended stored procedure is used to list of all the available OLE DB providers. It returns Provider Name, Parse Name and Provider Description. To get a list of all OLE DB providers for your SQL Server, run:

EXEC master..xp_enum_oledb_providers



xp_enumcodepages
This extended stored procedure can be used to list of all code pages, character sets and their description for your SQL Server. To see this, list, run:

EXEC master..xp_enumcodepages



xp_enumdsn
This extended stored procedure returns a list of all system DSNs and their descriptions. To get the list of system DSNs, run:

EXEC master..xp_enumdsn



xp_enumerrorlogs
This extended stored procedure returns the list of all error logs with their last change date. To get the list of error logs, run:

EXEC master..xp_enumerrorlogs


xp_enumgroups
This extended stored procedure returns the list of Windows NT groups and their description. To get the list of the Windows NT groups, run:

EXEC master..xp_enumgroups



xp_fileexist
You can use this extended stored procedure to determine whether a particular file exists on the disk or not. The syntax for this xp is:

EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]

For example, to check whether the file boot.ini exists on disk c: or not, run:

EXEC master..xp_fileexist 'c:\boot.ini'



xp_fixeddrives
This very useful extended stored procedure returns the list of all hard drives and the amount of free space in Mb for each hard drive. To see the list of drives, run:

EXEC master..xp_fixeddrives



xp_getnetname
This extended stored procedure returns the WINS name of the SQL Server that you're connected to. To view the name, run:

EXEC master..xp_getnetname



xp_readerrorlog
This extended stored procedure returns the content of the errorlog file. You can find the errorlog file in the C:\MSSQL7\Log directory, by default. To see the text of the errorlog file, run:

EXEC master..xp_readerrorlog