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

(@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 
    sysproperties s 
    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 

Upgrade from Team Foundation Sever 2005 to 2010 (TFS)


What a pleasant surprise I had upgrading our Team foundation server 2005 to 2010.

What I did, this was me stumbling through the process so if you want a more formal approach follow the many documents online:

  • Took a VM ware snapshot of the server (SQL and Application Tier)
  • Ran uninstaller for from Add/Remove programs of the server for anything TFS
  • Ran installer for windows share point 3.0 and allowed it to upgrade in place
  • Upgraded SQL server 2005 to 2008 in place
  • Moved the virtual server to ESX from VMWare Server for 2G memory requirement at install
  • Ran installer for Team Foundation Server
  • Ran configuration wizard for Team Foundation Server


Now I have a super fast TFS 2010 server! I do so thank everyone who worked to make this so painless. In my mind I had set aside a week of pain to get this job done, as it happened it all took about a day, most of which was waiting for installers and converting the VM machine (needed a disk resize).

OpenID for B2B websites

About OpenIDopenid-logo-wordmark

OpenID provides a mechanism by which a user may be authenticated by using a third party. This means the site consuming the Open ID does not have to maintain tables with user ID and password hashes (although providing parallel support is advisable). When a user chooses to login, they are redirected to the third party site, Google for instance, in order to authenticate themselves and come back to the originating site on a call back URL. Similar to the way card payment providers send the browser to the bank to enter the verified by Visa or Mastercard secure code authentication. The call back will contain a valid authentication token if the authentication was a success. The newer versions of Open ID also allow details of the user stored by the third party to be retrieved from their profile. Details such as email address may be requested or required to be sent. This information may then be used to pre-fill registration forms or wipe out the need for registration forms in extreme examples.

Most internet users already have an Open ID, even though most don’t know it. This is courtesy of large internet players such as Google and Yahoo. Account holders on these services and many others automatically create an Open ID for users. The appeal of OpenID to the website owner is that the user will find it easy to authenticate themselves with a registration process that is minimised or eliminated. The attraction to the user is that they finally do not have to manage hundreds of passwords for all the sites they visit because everyone uses a unique password every time -right?

Implementing OpenIDOpenID Selector Login Icons

I considered OpenID a while back but dismissed it as too geeky for a mainstream eCommerce site to use. The integration was painful at the time and the user experience was poor. Now times have moved on and there are libraries that can be included in a project that provide for easy integration of OpenID such as DotNet OpenAuth. There are also javascript projects that address some of the clunky user interface issues experienced in the early days. They present the OpenID login as familiar service icons, see Openid Selector Project. There are variants that give graphical drop down boxes for the available services too.

Using OpenID for B2B

I am in no doubt that for consumer sites such as Kmartin the US and for personal sites like blogs. they can benefit from allowing users to login using OpenID. The question is should we use OpenID today for a business to business website? It is appealing that people can click on familiar Facebook or Google icons to login to the site but the fear is that by doing so, all too often the captured identity of the person would be domestic, not their professional work identity.

As time goes on the way identities are managed by individuals may converge but at the moment there is too much risk of fragmenting database marketing activity data with these  personal profiles. It is not unreasonable to think that a customer logging into a site with Facebook will end up, through ignorance, providing their home account details. Domestic email addresses are an inappropriate channel to communicate with them through for order confirmations, marketing initiatives, etc.

Is there no work around?

Perhaps when a new OpenID is authenticated and comes back into the site we could ask if the email address of the OpenID is the same email address they want to correspond with us on and allow the user to add a correspondence address. The only problem is that we have not authenticated against that address if they do this that sort of feels like a bad idea. Certainly this does not help us with say rules that automatically map domain names of email addresses to account numbers as we can not allow this person to place an order on an account for which we cannot guarantee they are authorised to do so. It would be interesting if readers have found ways to work around this issue.

Security concerns

A college raised the concern of what if the OpenID password is compromised by a key logger or similar? Would this make a site using OpenID open to fraud? It does, although in reality most people use the same passwords across most sites, thus the risk is not greatly increased, but it is certainly present. It is a concern and a better understanding of the risks before committing to any OpenID scheme would be required. There is a lack of awareness as to how important protecting these big portal logins is amongst the internet population. Many are not aware the amplitude of damage that may be caused by compromising, say a Google identity. Think of how many site could be accessed in that user’s name.

Another related question regards customers with many branch offices or larger teams of people. It is possible to for a suitably privileged user in that organisation to create and manage user accounts for their staff on our site for those other workers. OpenID would not change this as the association between an account created with an email address can be picked up when a user logs in as the OpenID provider will return an email address for the user logging in. This is assuming we have got the same email address as the OpenID provider, bit assumption given what we are saying about domestic logins earlier.


It is disappointing that we will not be implementing OpenID as it would clearly provide benefits to users that understand how to manage their identities in that framework. Unfortunately for B2B websites I don’t think the paradigm is well enough understood by the end users and hence confusion may ensue leading to long telephone support calls with customers.

It is reasonable to assume that non IT workers have not created the appropriate professional and domestic identities with the authenticating sites. One exception is Linkedin , promoted as a professional network here people are more likely to have set up accounts in the context of their employment. Unfortunately as far as I am aware Linkedin does not support OpenID yet.


Rebuild sort order after delete or insert of record

Using TSQL Ranking Function on a sort column

Where there is a sort column containing numbers from 1..n representing the order in which a list of items in the database should be displayed, you have problems managing the insert and delete of records. Insert needs the records to be moved up that are above the sort order of the item getting inserted. Delete will leave a hole in the sort order.

Here you may see how the TSQL Ranking and Partitioning functions helps out.


WITH TempNewsTable (NewSort,OriginalSort) AS
FROM dbo.NewsSummaries) 
UPDATE TempNewsTable 
SET OriginalSort=NewSort


Here the sort column is regenerated based on the existing sort order, any gaps will be filled in.

If we want to insert a record then a tweak will leave us a gap,

WITH TempNewsTable (NewSort,OriginalSort) AS
FROM dbo.NewsSummaries) 
UPDATE TempNewsTable 
SET OriginalSort=NewSort+1 
WHERE SortOrder >= @InsertedItemSortOrder

Where @InsertedItemSortOrder is the sort order of the item we are inserting. A whole is left for the new row to be inserted.

Update is similar again, make a hole for new item and renumber to close up the hole we leave by moving the sort order of the item we are editing.