Pro SQL Server 2012 Reporting Services

CHAPTER 2  REPORT AUTHORING: DESIGNING EFFICIENT QUERIES

Figure 2-1 shows a graphical layout of the eight tables and how they’re joined.

Figure 2-1. Viewing the sample application’s database tables

Knowing Your Data: A Quick Trick with a Small Procedure

For every report writer, familiarity with the location of the data in a given database can come only with
time. Of course, having a database diagram or schema provided by a vendor is a useful tool, and we have
the luxury of that here, but this isn’t always available. One day, faced with the dilemma of trying to find
the right table for a specific piece of missing data, we decided to put together a stored procedure, which
we named sp_FieldInfo. It returns a list of all the tables in a specific database that share field names,
typically the primary or foreign key fields. For example, in the healthcare database, if you want a list of
tables that contain the PatID field (the patient’s ID number that’s used to join several tables), you would
use the following command:

sp_FieldInfo PatID

The output would be similar to that shown in Table 2-1.