DBA and SysAdmin World

September 8, 2008

Useful SQL Server Commands

Filed under: SQL Server — Tags: , — solihinho @ 1:08 AM
SQL Server
Since I am be a DBA, there are some system SPs (Stored Procedures) and DBCC (Database Console Commands) that I use regularly on my daily activities for display some information and for maintain the database.

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 Rolling Eyes)
  • 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
Idea We can look into the code of the system SP by using sp_helptext ‘SP name’ on master database. Sometimes we need add some information that not provide on those SP i.e : we can combine sp_who2 and DBCC INPUTBUFFER become more useful command (I will discuss about this later). So just look into on the script, modified it and save as become another SP. Don’t replace current SP.
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: