SQL Server Advanced commands

A

There are lot of information available in SQL which we are not aware of, we can virtually get any details from SQL server.

These are some SQL server commands which are very useful. Lot of hidden information about Server and database is provided by these commands.

Try these command once and you will realise what information you can retrieve in a single execution.

This will surely save time and will be really handy when used.

sp_helpdb

This gives you information about all databases in the instance or specific information about one database.

Sample Code:

• sp_helpdb
• sp_helpdb databasename

 

fn_virtualfilestats

This command will show you the number of read and writes to a data file.  Use sp_helpdb with the database name to see the logical file numbers for the data files and the database id.

Sample Code:

• SELECT * FROM :: fn_virtualfilestats(dabaseid, logicalfileid)        
• SELECT * FROM :: fn_virtualfilestats(1, 1)

 

fn_get_sql()

Returns the text of the SQL statement for the specified SQL handle.  This is similar to using DBCC INPUTBUFFER, but this command will show you additional information.  This can also be embedded in a process easier then using the DBCC command

Sample Code:

• DECLARE @Handle binary(20)
  SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52 SELECT * FROM ::fn_get_sql(@Handle) 

 

sp_lock

This command shows you all of the locks that the system is currently tracking  This is similar to information you can see in Enterprise Manager.

Sample Code:

• sp_lock
• sp_lock spid
• sp_lock spid1, spid2

 

sp_help

This command gives you information about the objects within a database.  The command without an objectname will give you a list of all objects within the database.

Sample Code:

• sp_help
• sp_help objectname

 

sp_who2

Gives you process information similar to what you see when using Enterprise Manager.

Sample Code:

• sp_who2
• sp_who2 spid

 

sp_helpindex

Gives you information about the indexes on a table as well as the columns used for the index

Sample Code:

• sp_helpindex objectname

 

sp_spaceused

This command shows you how much space has been allocated for the database (or if specified an object) and how much space is being used.

Sample Code:

• sp_spaceused
• sp_spaceused objectname

 

DBCC CACHESTATS

Displays information about the objects currently in the buffer cache.

Sample Code:

• DBCC CACHESTATS

 

DBCC CHECKDB

This will check the allocation of all pages in the database as well as check for any integrity issues.

Sample Code:

• DBCC CHECKDB

 

DBCC CHECKTABLE

This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.

Sample Code:

• DBCC CHECKTABLE (‘tableName’)

 

DBCC DBREINDEX

This command will reindex your table.  If the indexname is left out then all indexes are rebuilt.  If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.

Sample Code:

• DBCC DBREINDEX (tablename, indexname,fillfactor)
• DBCC DBREINDEX (authors, ”, 70)
• DBCC DBREINDEX (‘pubs.dbo.authors’, UPKCL_auidind, 80)

 

DBCC PROCCACHE

This command will show you information about the procedure cache and how much is being used.  Spotlight will also show you this same information.

Sample Code:

• DBCC PROCCACHE

 

DBCC MEMORYSTATUS

Displays how the SQL Server buffer cache is divided up, including buffer activity.

Sample Code:

• DBCC MEMORYSTATUS

 

DBCC SHOWCONTIG

This command gives you information about how much space is used for a table and indexes.  Information provided includes number of pages used as well as how fragmented the data is in the database.

Sample Code:

• DBCC SHOWCONTIG
• DBCC SHOWCONTIG WITH ALL_INDEXES
• DBCC SHOWCONTIGtablename

 

DBCC SHOW_STATISTICS

This will show how statistics are laid out for an index.  You can see how distributed the data is and whether the index is really a good candidate or not.

Sample Code:

• DBCC SHOW_STATISTICS (tablename, indexname)

 

DBCC SHRINKFILE

This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to.  Use the sp_helpdb command along with the database name to see the actual file names used.

Sample Code:

• DBCC SHRINKFILE (filename, size in MB)
• DBCC SHRINKFILE (DataFile, 1000)

 

DBCC SQLPERF

This command will show you much of the transaction logs are being used.

Sample Code:

• DBCC SQLPERF(LOGSPACE)

 

DBCC TRACEON

This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.

Sample Code:

• DBCC TRACEON(traceflag)

 

DBCC TRACEOFF

This command turns off a trace flag.

Sample Code:

• DBCC TRACEOFF(traceflag)