Shortcut to Script Individual Columns from a Table-Valued Function in SSMS

I noticed recently that if you use SQL Server Management Studio to script out a Select statement from a table-valued function, SSMS generates a Select *. In the following screen shot, I'm scripting out a Select statement from a table-valued function that's in the AdventureWorks2012 database (this TVF is just an example, not my real function).

SSMSShortcut1.jpg

In my situation, the Select statement being generated from my inline table-valued function will be going into an SSIS package as the source query for the data flow.  A Select * is generally not thought of as a good thing in most cases anyway, but in an SSIS package usage of a Select * does introduce risk.  If a new column gets added, deleted, or changed in database object - but not in the corresponding SSIS package - then the SSIS package will error out. If your change control processes and regression testing procedures ensure that the function and the SSIS package will always remain in conjunction with each other, then maybe the Select * isn't a big deal. However, ever since my phone rang in the middle of the night about 7 years ago because I made this exact mistake, my preference has been to reduce the risk of the Select * by specifying the columns going into the SSIS pipeline.

So, at this point I was (a) curious about why SSMS scripts out the Select * the way it does for a table-valued function, and (b) thinking about how to get all the individual columns scripted out from my inline table-valued function in a quick way.  Here's where the super duper awesome SQL community comes in. I asked about this SSMS behavior on Twitter with the #sqlhelp tag. Robert Davis (@SQLSoldier) responded that a table-valued function is a routine, like a procedure, and so the output can be variable. This was good insight for me. And MartinSmith (@MartinSmith_) gave me a handy dandy tip about using an SSMS shortcut to script out the Select statement with columns specifically named.

Highlight the "Select * From TVF()" in the SSMS query window and then "Ctrl + Shift + Q" as shown in the following screen shot. The Query Designer window will pop up.  Just copy the new Select statement from the Query Designer window.

SSMSShortcut2.jpg

And, with that SSMS shortcut, we have a quick way to get the full "Select Column1, Column2, Column3 From TVF()" for use as the source query in SSIS (or whatever other purpose).

Resources to Study for Exam 70-461

Exam 70-461 is the SQL Server 2012 Querying exam.  There's some aspects of T-SQL that I've not used at all like XML, and certainly a few that I will want to brush up on before heading to this exam.  Here's a few places I found for studying:

Webpages

Books

Videos

Practice Questions

Don't forget that Microsoft often offers the Second Shot program where you get an exam retake for free. Current offers are available here:  http://www.microsoft.com/learning/en-us/offers.aspx.

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

 

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