Useful SQL Server Commands

Useful SQL Server Commands

Commands For Display Information

• Reports information about a specified database or all databases
  use : sp_helpdb
• Reports information about the indexes on a table or view
  use : sp_helpindex
• Returns statistics information about columns and indexes on the specified table
  use : sp_helpstats
• Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure
  use : sp_helptext
• Reports information about a database object
  use : sp_help
• Returns the physical names and attributes of files associated with the current database. use : sp_helpfile
• Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause
  use : sp_tables
• Returns column information for the specified tables or views that can be queried in the current environment
  use : sp_columns
• Reports information about locks
  use : sp_lock
• Displays the number of rows, disk space reserved, and disk space used by a table
  use : sp_spaceused
• Displays or changes global configuration settings for the current server
  use : sp_configure
• Provides information about current users and processes
  use : sp_who or sp_who2 (undocumented on BOL )
• Displays the current distribution statistics for the specified target on the specified table
  use : DBCC SHOW_STATISTICS
• Displays fragmentation information for the data and indexes of the specified table
  use : DBCC SHOWCONTIG
• Provides statistics about how the transaction-log space was used in all databases
  use : DBCC SQLPERF
• Displays the last statement sent from a client
  use : DBCC INPUTBUFFER

Commands For Database And Server Maintenance

• Rebuilds one or more indexes for a table in the specified database
  use : DBCC DBREINDEX
• Defragments indexes of the specified table or view
  use : DBCC INDEXDEFRAG
• Runs UPDATE STATISTICS against all user-defined and internal tables in the current database
  use : sp_updatestats
• Creates single-column statistics for all eligible columns for all user tables and internal tables in the current database. The new statistic has the same name as the column where it is created.
  use : sp_createstats
• Shrinks the size of the data files in the specified database
  use : DBCC SHRINKDATABASE
• Shrinks the size of the specified data file or log file for the related database
  use : DBCC SHRINKFILE
• Closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that the new log has been created
  use : sp_cycle_errorlog or DBCC ERRORLOG