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.
