Pro SQL Server 2012 Reporting Services

CHAPTER 2  REPORT AUTHORING: DESIGNING EFFICIENT QUERIES

Table 2-1. Output of sp_FieldInfo

Table Name Field Name

PatCertDates PatID

PatDiag PatID

PatEMRDoc PatID

Trx PatID

Patient PatID

Admissions PatID

Armed with this information, you could at least deduce that, for example, the patient’s diagnosis

information is stored in the PatDiag table. However, table and field names aren’t always intuitively

named. When we encounter a database such as this from time to time, we can run a SQL Server Profiler

trace and perform some routine tasks on the associated application, such as opening a form and

searching for an identifiable record to get a starting point with the captured data. The Profiler returns the

resulting query with table and field names that we can then use to discern the database structure.

 Tip SQL Server Profiler is an excellent tool for capturing not only the actual queries and stored procedures

executing against the server, but also the performance data, such as the duration of the execution time, the central

processing unit (CPU) cycles and input/output (I/O) measurements, and the application that initiated the query.

Because you can save this data directly to an SQL table, you can analyze it readily, and it even makes a good

source of data for a report in SSRS.

Listing 2-1 displays the code to create the sp_fieldinfo stored procedure. You can find the code for

this query in the code download file CreateFieldInfo.sql in the SQL Queries folder.

Listing 2-1. Creating the sp_FielIinfo Stored Procedure

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME = 'sp_FieldInfo'

AND SPECIFIC_SCHEMA = 'dbo')

DROP PROCEDURE [dbo].[sp_FieldInfo]

GO

CREATE PROCEDURE [dbo].[sp_FieldInfo]

(

@column_name VARCHAR(128) = NULL

)