There are various in-built system catalog views, or metadata functions that you can use to check the existence of column in SQL Server tables.
To demonstrate this, first we create a table in SQL Server as shown below.
CREATE TABLE dbo.SampleTable (Id INT, Name VARCHAR(100), Address VARCHAR(150) )
Now we have a table named SampleTable, which contains three columns that is Id, Name and Address. As shown below.
SELECT * FROM dbo.SampleTable
Lets see the various methods to checking column existence.
Check column existence using INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.COLUMNS view which allows you to get information about all columns for all tables within a database.
Lets check whether the Name column does exist in table SampleTable or not.
Following T-SQL uses Information_Schema.Colum view to check the existence of column Name in table SampleTable.
IF EXISTS ( ย SELECT * ย FROM INFORMATION_SCHEMA.COLUMNS ย WHERE table_name = 'SampleTable' ย AND column_name = 'Name' ) SELECT 'Column exists in table' AS [Status] ; ELSE SELECT 'Column does not exist in table' AS [Status];
You can see, the column Name exists in table.
If we check for column RowId in table SampleTable, as we already know that column RowId does not exist in table, Lets check using Information_Schema.Columns view.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'SampleTable' AND column_name = 'RowId' ) SELECT 'Column exists in table' AS [Status] ; ELSE SELECT 'Column does not exist in table' AS [Status];
Check column existence using sys.columns
Sys.columns is another catalog views in SQL Server which returns a row for each column of an object that has columns, such as views or tables.
Lets check for the column Name using sys.columns view, whether it does exist in table SampleTable or not.
IF EXISTS(SELECT 1 FROM sys.columns ย ย WHERE Name = N'Name' ย ย AND Object_ID = Object_ID(N'dbo.SampleTable')) SELECT 'Column exists in table' AS [Status] ; ELSE SELECT 'Column does not exist in table' AS [Status];
You can see in below result, column Name exists in table.
Check column existence using COL_LENGTHย function
COL_LENGTH function is a SQL Server Metadata functions, which returns the defined length of a column, in bytes.
Lets see how this function returns the length of specified column in bytes. Lets check the length of column Name.
SELECT COL_LENGTH('[dbo].[SampleTable]','Name') As ColLen
You can see, It returns the length of the column Name that is 100, which also ensures that this column exists in table that’s why it has some length.
Following T-SQL uses COL_LENGTH function which returns the length of a column as we have seen above which satisfies the IF condition and returns ‘Column exists in tables’.
In case, if column does not exist in table the COL_LENGTH function returns null, that means column does not exist in table.
IF COL_LENGTH('[dbo].[SampleTable]','Name') IS NOT NULL SELECT 'Column exists in table' AS [Status] ; ELSE SELECT 'Column does not exist in table' AS [Status];
You can see query result, Column Name exists in table.
Also Read..
SQL Server Startup procedures Sp_Procoption
52,100 total views, 5 views today