Search
Twitter

Entries in T-SQL (5)

Wednesday
Mar022011

Finding Where Database Fields are Used in Procedures or Functions

This week I needed to quickly determine which database fields are being actively used or displayed within a report.  We’re working with a client to map their source data into the K12 data warehouse model provided by Mariner.  We wanted to cross-reference which fields are actively being used or not, for the purpose of determining how much work we should go to to populate particular fields that are difficult to retrieve or derive.  Because our SSRS reports rely upon stored procedures in SQL Server to perform the queries, I was able to query the system tables.

Query Results

The results of the query shown below are 3 columns:  the table, the column, and the procedure name or function name that uses the column. 

image

Query

The query looks for both stored procedures and functions, since we often utilize functions underneath stored procedures (more info on that here: Using Table Valued Functions to Support SSRS Reporting).

Key components of the query are as follows:

  • The initial table variable returns the object name and the full text (i.e., the create statement).  The full text is what’s searched in the last 2 lines of the where statement.
  • It is within the initial table variable that we specify to only search stored procedures or functions.
  • The final lines of the where statement searches the “ObjectText” from the table variable for the column name within it.  This is where the magic happens (and prevents bad effects from the cross join we had to do).

USE EdDW;

GO 

DECLARE @ObjectUsed TABLE

(  ObjectName NVARCHAR(128)

  ,ObjectText NVARCHAR(MAX)

)

 

INSERT INTO @ObjectUsed

     SELECT DISTINCT

             SysObj.Name

            ,LTRIM(RTRIM(SysComm.[text])) AS [Text]

     FROM [sysobjects] AS SysObj

            INNER JOIN [syscomments] AS SysComm

                   ON SysObj.Id = SysComm.ID

                 AND SysObj.[Type] IN('P','TF','IF','FN')

---------------

SELECT DISTINCT

     SysTbls.name AS [Table Name]

    ,SysCols.name AS [Column Name]

    ,ObjectUsed.ObjectName AS ObjectName

FROM [sys].[tables] AS SysTbls

     LEFT JOIN  [sys].[columns] AS SysCols

                ON  SysTbls.[object_id] = SysCols.[object_id]

     LEFT JOIN  [sys].[objects] as SysObj

                ON  SysTbls.[object_id] = SysObj.[object_id]

     LEFT JOIN [sys].[types] AS SysTyp

                ON  SysCols.user_type_id = SysTyp.[user_type_id]

     CROSS JOIN @ObjectUsed AS ObjectUsed

WHERE SysTbls.name IS NOT NULL

     AND SysCols.name IS NOT NULL

     AND ObjectText LIKE '%' + SysCols.name + '%'

     AND ObjectText LIKE '%' + SysTbls.name + '%'

ORDER BY

     SysTbls.name

    ,SysCols.name

 

Saturday
Nov132010

Loading and Using A SQL Server Database Image

This blog entry discusses how to add an image to SQL Server for storage within the database, followed by how to use it in SSRS.

When including an image within a SQL Server Reporting Services report, you have 3 options:

  1. Embedded.  The image is embedded within the report.  This is my least favorite choice, as it’s not easily reusable and maintenance of a change would be a big headache if very many reports existed with individual embedded images.
  2. External.  The image is loaded to the Report Project and may be reused among multiple SSRS reports.  This approach was discussed in this ImagePaths in SSRS entry.  
  3. Database.  The image is loaded to a SQL Server database and, like option 2, may be reused among multiple SSRS reports.  Because it’s stored in the database, it can be used for many other types of solutions as well.  This is the most flexible of the 3 options - and the topic of the rest of this entry.

Using OPENROWSET to Insert Image Into Table

1.  First, let’s create a table in SQL Server Management Studio to hold the image file.

CREATE TABLE dbo.Images

(

      [ImageID] [int] IDENTITY(1,1) NOT NULL,

      [ImageName] [varchar](40) NOT NULL,

      [OriginalFormat] [nvarchar](5) NOT NULL, 

      [ImageFile] [varbinary](max) NOT NULL

 )    

Note that the data type we’re using for the image is varbinary(max) instead of the image data type.  That’s because the image data type is being removed in a future version of SQL Server.

2.  Right-click this dog image and save it to your c:\ drive as “MyImage.png”.

  

3.  Now let’s insert the dog image into the database table we created in step 1.

INSERT INTO dbo.Images

(

       ImageName

      ,OriginalFormat

      ,ImageFile

)

SELECT

      'Sample Image'

      ,'png'

      ,ImageFile

FROM OPENROWSET(BULK N'C:\MyImage.png', SINGLE_BLOB) AS ImageSource(ImageFile);

That’s it!  Now you’re ready to use it.  I like to include an “Original Format” field so that I can easily remember what to set for the MIME type in SSRS.

SSRS Image Properties

In this section we’ll discuss how to use the database image in SSRS.

1.  First, create a dataset within your report which contains the following query.  I’m calling my dataset “HeaderImage.”

SELECT ImageFile

FROM dbo.Images

2.  From the Toolbox, drag an Image Report Item onto the body of the report.  Configure it as follows:

Select the image source:  Database

Use this field:  =First(Fields!ImageFile.Value, “HeaderImage”)

Use this MIME type:  image/png    (this is the MIME type & subtype, which classifies file types on the web for consistent handling between browsers & O/S)

And voila, you have a drooling puppy on your report - just what you've always wanted! 

Finding More Information

About OPENROWSET

Adding Images to a Report 

 

Friday
Nov052010

Querying Extended Properties on SQL Server Columns

Using extended properties is a terrific way for a database to be self-documenting.  I find extended properties on columns to be particularly useful.  The extended property may contain whatever you need, but usually it serves to describe the column and may also provide sample values or situations when a particular value would be used.  For example:

Contains a value of 1 if the day is the last day in the academic year. Otherwise, 0.    

I found myself wanting to query some existing extended properties so I could better understand some fields.  Column level extended properties are the focus of the following query:

SELECT

     SysTbls.name AS [Table Name]

    ,SysCols.name AS [Column Name]

    ,ExtProp.value AS [Extended Property]

    ,Systyp.name AS [Data Type]

    ,CASE WHEN Systyp.name IN('nvarchar','nchar')

               THEN (SysCols.max_length / 2)

          WHEN Systyp.name IN('char')

               THEN SysCols.max_length

          ELSE NULL

          END AS 'Length of Column'

    ,CASE WHEN SysCols.is_nullable = 0

               THEN 'No'

          WHEN SysCols.is_nullable = 1

               THEN 'Yes'

          ELSE NULL

          END AS 'Column is Nullable'   

    ,SysObj.create_date AS [Table Create Date]

    ,SysObj.modify_date AS [Table Modify Date]

FROM sys.tables AS SysTbls

   LEFT JOIN sys.extended_properties AS ExtProp

         ON ExtProp.major_id = SysTbls.[object_id]

   LEFT JOIN sys.columns AS SysCols

         ON ExtProp.major_id = SysCols.[object_id]

         AND ExtProp.minor_id = SysCols.column_id

   LEFT JOIN sys.objects as SysObj

         ON SysTbls.[object_id] = SysObj.[object_id]

   INNER JOIN sys.types AS SysTyp

         ON SysCols.user_type_id = SysTyp.user_type_id

WHERE class = 1 --Object or column

  AND SysTbls.name IS NOT NULL

  AND SysCols.name IS NOT NULL

The results of the above query:

I went on to create a simple SSRS report from it, adding the Table Name as a multi-select parameter:

Hopefully you have access to a tool like PowerDesigner to generate this type of report.  If you can’t spring for a data modeling tool this technique could be useful.  Or, if you have a situation where the data model could get out of date with the database structure (i.e., it’s not mandatory that the DDL be generated from the data model), this type of query could be useful.

Finding More Information

Viewing Extended Properties  

Using Extended Properties on Database Objects

Easy Extended Properties 

Wednesday
Sep222010

Using Dual Join to Dimension to Retrieve All Historical Fact Table Rows

I am a big proponent of the Kimball method of data warehousing.  The denormalized table structure, with Type 1/2/3 dimensions, and surrogate keys provide a lot of benefits for querying and reporting.  However, there is one situation that I seem to run into every once in a while, at which time I have that “Oh yeah” moment. 

The situation:  Records for a student have changed over time within a Type 2 dimension table.  We have two goals: 

Goal (a):  Retrieve all historical fact records (regardless of how the surrogate key value may or may not have changed at any point in time).

Goal (b):  Retrieve the current dimension data in the report.

First, let’s review the dimensional data for Danny Zuko.  (And before we go any farther, let’s just agree that Grease is one of the finest movies ever made, shall we?)

Sample Dimension Records:

In the above table, the Student ID is our surrogate key in the data warehouse.

Note that Student ID #3 is the current record, showing Danny’s current address and current major, both of which have changed over time.

Next let’s look at some fact records. 

Sample Fact Records:

Let’s say we have a requirement to produce a report of attendance history by individual student.  If we based the query on the current StudentID (3), we’d inadvertently drop fact table records associated with Student IDs 1 and 2.  Following is a solution we routinely put in place in our reporting queries to ensure all history is captured.

Sample query:

SELECT

    DSt.StudentName    

   ,DSt.Major  

   ,DSt.AddressFull    

   ,FSA.FromDateID

   ,FSA.ToDateID

   ,DaysAttended

   ,DaysAbsent 

FROM DW.DimStudent DSt

   INNER JOIN DW.DimStudent DStAll

        ON DSt.NaturalKey = DStAll.NaturalKey

   INNER JOIN DW.FactStudentAttendance FSA

        ON DStAll.StudentID = FSA.StudentID

WHERE DSt.CurrentRecord = 'Yes'

How our two goals are satisfied with this query:

Goal (a):  Retrieve all historical fact records (regardless of how the surrogate key value may or may not have changed at any point in time).  The above query joins DimStudent back to itself a second time, using the natural key.  The join to the fact table records are based on the 2nd DimStudent table (i.e., the DStAll alias), so we are assured of retrieving all fact records.

Goal (b):  Retrieve the current dimension data in the report.  The WHERE statement restrictions are based on the first DimStudent table (i.e., the DSt alias).

This approach assumes that the natural key is retained within the dimension, but not within the fact.  It also assumes that the natural key is reliable data. 

Any comments, thoughts or alternative suggestions would be welcomed.

Sunday
Aug152010

Using Table-Valued Functions to Support SSRS Reporting

I'm a big fan of Table-Valued Functions, as a way to centralize reusable logic in reports produced by SQL Server Reporting Services.  I’ll refer to Table-Valued Functions as TVFs for the remainder of this article. 

Full documentation for TVFs can be found here:  MSDN

To support SSRS reporting, the way I currently tend to use TVFs is as follows:

 

Reasons I find the above process valuable:

  • Centralize Logic.  Currently we utilize a TVF as a centralized way to find an initial set of data, based on a parameter passed.  Every reporting stored procedure joins to this centralized TVF so the dataset is retrieved the same way every time.  In my case, it’s a population of students made available to the report.  It goes without saying that standardizing this logic saves a lot of time if it has to change.
  • Replacement for a Stored Procedure.  For SSRS reports, we use stored procedures as the source for each dataset (in fact, the DBA doesn’t permit queries from within an SSRS dataset - this is enforced by allowing Execute permissions on the schema we need, but not Select).  Because we want to centralize a portion of the logic that isn’t report-specific, a developer’s first instinct could be to call this centralized logic from another stored procedure.  However, that won’t work because the SSRS report server can only process the first result set returned.  Therefore, we utilize a TVF lieu of another stored procedure so this process works for SSRS reporting.
  • Replacement for a View.  For SSRS reports, we pass a staff ID as a parameter in order to narrow down the set of data returned (for example:  the set of students a teacher is allowed to see).  Because a view cannot accept parameters, using a TVF for the centralized part of our logic instead of a view is very useful.
  • Allows Complex Logic.  We often use Common Table Expressions (CTEs) to arrive at a set of data in a multi-step process.  A TVF allows you to use the power of CTEs to arrive at the final Select statement.
  • Ease of Use.  Since you can join to a TVF as any other database object, its syntax is very easy to use.

Below is my simplified “cheat sheet” for the syntax.

Creation of the Table-Valued Function:

CREATE FUNCTION RPT.ufnMyFunctionName

   ( 

   @Variable1 varchar(10)

   )

RETURNS

   @TableVariableName TABLE

   (

    [Field1] nvarchar(20)

   ,[Field2] nvarchar(20)

   )

AS

BEGIN

INSERT INTO @TableVariableName

SELECT

    T2.Field1

   ,T2.Field2

FROM DW.Table1 AS T1

   INNER JOIN DW.Table2 AS T2

      ON T1.Field1 = T2.Field1

WHERE T2.Field3 = @Variable1

RETURN

END

 

Usage of the Table-Valued Function within a Stored Procedure:

CREATE PROCEDURE RPT.uspReportingProcName

AS

SELECT

    T3.Field1

   ,T3.Field2

   ,T3.Field3

   ,T4.Field1

   ,T4.Field2

   ,T5.Field1

FROM DW.Table3 AS T3

   INNER JOIN RPT.ufnMyFunctionName(@Variable1) AS T4

      ON T3.FieldA = T4.FieldA

   INNER JOIN DW.Table5 AS T5

      ON T3.FieldB = T5.FieldB