Blog Post

5 Tips for Finding SQL Server System Data


Regardless of the type of development (application or reporting) that you are performing on SQL Server.  There are 5 items within SQL Server that I consistently use to help me design and troubleshoot solutions.


SP_Who is a system stored procedure that returns information on what is currently running on your SQL box.  It shows you the following information:

  • SPID
  • ECID
  • Status
  • Login name
  • Host Name
  • Block
  • DB Name
  • Command
  • Request ID

I use the sp often to see what processes are actively running and if there are any blocks on the system.

Complete information can be found here: SP_Who

DBCC ShowContig

DBCC ShowContig returns information on your data and indexes within a specific database on your server.  Using DBCC ShowContig allows you to see the fragmentation and scan density of your indexes.  These 2 metrics can help you determine any issues with your indexes, a critical step for business intelligence applications.  In SQL 2014, this command is being replaced with sys.dm_db_index_physical_stats.


This is a system view that returns one record for every Stored Procedure and function in your database.  There are 2 columns in this view that I leverage often the ROUTINE_NAME and ROUTINE_DEFINITION.  The ROUTINE_NAME returns the name of the sp or function.  The ROUTINE_DEFINITION is what I use to perform lookups if I need to determine where a specific column is being used in the database to make modifications or troubleshoot issues.

Complete information can be found here: Information_Schema.Routines


The Information_Schema.Columns returns system information on the tables and columns in the database.  I use this to find what columns and datatypes when researching databases versus looking them up in the table designer on each table.

Complete information can be found here:


I generally use a lot of linked servers from a central reporting database to other systems that I need to pull data from.  The stored procedure sp_helpserver will return all information on linked servers on your SQL Server system.


The post 5 Tips for Finding SQL Server System Data appeared first on Derek Wilson - Blog.