Dynamically fetching column names and meta descriptions TSQL

Some time ago I made to some large width SQL server tables providing for lots of spare fields consisting of various data types. These tables could be used for system stakeholders to add new things they needed to store against products in our ERP system over time without disturbing me for more fields. The SQL server description property for the column was used to populate the labels on the GUI forms dynamically with the meaning of the field. Thus there is no need for a separate table to hold the label information and that also means that the information is at hand when working in SQL.

At the time I used a TSQL snippet from the web to grab the descriptions. The snippet used the sys.columns, sys.extended_properties to gather the info required to for the GUI labels. The query has been causing problems over the years, running for up to 2 seconds is not uncommon. After finally getting a reason to revisit the forms, I went to look at the query plan for and uncovered the cause. A scan due to the where clause that contained WHERE OBJECT_NAME(c.object_id)=@TableName

To avoid calling the OBJECT_NAME function for each comparison row in the DB, I added the sys.objects table to create a lookup join that fetches the object id for the table owning the columns and now the query is running in sub-second time spans. This is a great improvement, resulting in a snappy GUI form open, unlike the sluggish experience the users were getting previously. This points out again how you have to take care calling functions for each row as it expensive, set operations almost always are the way to go!

The resulting improved stored procedure containing the optimised query for my reference is below. I wish I could reference and acknowledge the originator of this query, but it was in the days before I grew wise enough to retain references to the origin of code snippets in my TSQL comments.

TSQL  to retrieve table column names and meta description for a SQL server table

 
CREATE PROCEDURE [SY_GetTableFields]
(@TableName as varchar(255))
AS
 
IF CAST(SUBSTRING(CAST(SERVERPROPERTY('productversion') 
  as varchar),1,1) as int) >= 9
BEGIN      -- This is a SQL 2005 machine      
SELECT    [Table Name] = OBJECT_NAME(c.object_id),      
        [Column Name] = c.name, 
        [Description] = ex.value, c.object_id
        FROM  sys.objects sobj
            JOIN sys.columns c 
                ON c.object_id = sobj.object_id       
            LEFT JOIN   sys.extended_properties ex
                ON ex.major_id = c.object_id           
            AND ex.minor_id = c.column_id 
            AND OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 
            AND ex.name = 'MS_Description' 
        WHERE  sobj.name= @TableName
        ORDER  BY  c.column_id
END
ELSE
BEGIN
SELECT 
    [Table Name] = i_s.TABLE_NAME, 
    [Column Name] = i_s.COLUMN_NAME, 
    [Description] = s.value 
FROM 
    INFORMATION_SCHEMA.COLUMNS i_s 
    LEFT OUTER JOIN 
    sysproperties s 
ON 
    s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME) 
    AND s.smallid = i_s.ORDINAL_POSITION 
    AND s.name = 'MS_Description' 
WHERE 
    OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME),
   'IsMsShipped')=0 
    AND i_s.TABLE_NAME = @TableName 
ORDER BY 
    i_s.TABLE_NAME, i_s.ORDINAL_POSITION
 
END

Add comment

Loading