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 containedWHERE 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 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
[Table Name] = i_s.TABLE_NAME,
[Column Name] = i_s.COLUMN_NAME,
[Description] = s.value
LEFT OUTER JOIN
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
AND i_s.TABLE_NAME = @TableName