How to check if a column exists in a table – SQL Server

Checking a column existence in a table is very simple in SQL Server, but you should be careful while doing it on Azure environment. We simply ignore the performance consideration while writing SELECTs against systems tables/views/dmvs etc. Recently a stored proc started working very very slow on a production server, which is hosted on Microsoft Azure (SQL as a service). The same stored proc was working well on the UAT , which is a replica of live with less hardware configurations and hosted on a virtual server on another cloud. I first thought, it might be an issue with the network but later I realised network is not the concern at all. I started digging into the proc and found that the culprit is a system view, which I was using to check the column existence in SQL Server table by doing a IF EXISTS. As soon as I replaced that statement with a system function, the stored proc started working well like it used to be on UAT.

I don’t know the exact reason of the slowness, but be cautious while fetching data from system views on Microsoft Azure.

Using COL_LENGTH (Recommended)

Using COLUMNPROPERTY (Recommended)

Using sys.columns

Using INFORMATION_SCHEMA.COLUMNS

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: