Important DBCC Commands and Stored Procedures

List of DBCC Commands

DBCC stands for Database Console Commands.

1.DBCC HELP(<command>)

         Returns syntax information for the specified DBCC command.

2.DBCC checkdb

         Checks the logical and physical integrity of all the objects in the specified database

3.DBCC INPUTBUFFER(spid)

         It is a command used to identify the last statement executed by a particular SPID. 

4.DBCC SHOWCONTIG      

Displays the fragmentation information for the data and indexes of the specified table or view.

5.DBCC config

         It returns information of server configuration

6.DBCC dbinfo [‘dbname’]

         It gives metadata information of the database like database creation id, date,compatibility level and last log backup time etc. also db_dbcclastknowngood.

7.DBCC detachdb[‘dbname’]

         This command can be used to detach a database from the SQL Server instance.

8.DBCC DBRecover(DBName)

This command can be used to manually recover the database. Normally databases are recovered at system startup, but for some reason if they fail, we can use this to recover the database manually.

9.DBCC errorlog

 This command closes the current errorlog and creates a new errorlog. This command is similar to sp_cycle_errorlog.

10.DBCC log(DbID)

This command is used to display the log record information from the specified database transaction log.

11.DBCC UpgradeDB(DBName) 

 This command is used to upgrade the system objects of specified database to the version of database engine.

12.DBCC loginfo [‘database name’]

DBCC LOGINFO returns information about virtual log files within the physical transaction log.

13.DBCC memorystatus

This command provides a snapshot of the current memory status of Microsoft SQL Server.

14.DBCC sqlperf

Provides transaction log space usage statistics for all databases. In SQL Server it can also be used to reset wait and latch statistics.

15.DBCC sqlmgrstats

This command accepts no parameters and returns the number of memory pages used, the total number of Transact-SQL statements that have been cached and the number of false hits.

16.DBCC traceoff

Disables the specified trace flags.

17. DBCC traceon

Enables the specified trace flags.

List of Stored procedures

1.    Sp_help(objectname)

To give a detailed information about the object.

2.    Sp_monitor

Displays statistics about Microsoft SQL Server.

3.    Sp_send_dbmail

To manually send an outgoing mail.

4.    Sp_server_info

stored procedure to get server level information for SQL Server

5.    Sp_datatype_info

results in all data types that are accepted by you sql server version on their corresponding data types

6.    Sp_depends

Displays information about database object dependencies, such as the views and procedures that depend on a table or view

7.    Sp_helprotect

Returns a report that has information about user permissions for an object, or statement permissions, in the current database.

8.    Sp_getapplock

Places a lock on an application resource

9.    Sp_who2

shows all the sessions that are currently established in the database

10.Sp_helpindex

Reports information about the indexes on a table or view

11.Sp_lock

Provides information about locks.

12.Sp_databases

Lists all databases in an instance or that are accessible through a gateway

13.Sp_catalogs

Returns the list of catalogs in the specified linked server. This is equivalent to databases in SQL Server.

14.Sp_columns

Returns column information for the specified objects that can be queried in the current environment.

15.Sp_statistics

Returns a list of all indexes and statistics on a specified table or indexed view

16.Sp_tables

Returns a list of objects that can be queried in the current environment. This means any table or view, except synonym objects.