SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

Business Intelligence and Enterprise Architecture

Derek Wilson delivers tactical and strategic Business Intelligence and Enterprise Architecture solutions. His primary focus in on Microsoft SQL Server technologies and aligning business problems to technology solutions. He architects BI solutions leveraging SQL Server, SharePoint and any other technologies that help his clients achieve better data driven decisions. By leveraging the information learned while collecting requirements for BI projects, he helps align business processes to technology helping further organizations Enterprise Architecture. He is an author, trainer, blogger and has been using SQL Server since version 6.5.


Leave a comment on the original post [derekewilson.com, opens in a new window]

Loading comments...