Skip to content

SqlSkull

SQL Server | SSRS | SSIS | Power BI | DAX
  • Home
  • About Us
  • Microsoft SQL Server
    • SQL Basics Concepts
    • SQL Advance Concepts
    • SQL Interview Q & A
    • SQL Server Tricks & Tips
  • SSRS
  • SSIS
  • POWER BI
  • Python with ML
  • Microsoft Azure
  • HTML Color picker

SqlSkull

SQL Server | SSRS | SSIS | Power BI | DAX
  • Home
  • About Us
  • Microsoft SQL Server
    • SQL Basics Concepts
    • SQL Advance Concepts
    • SQL Interview Q & A
    • SQL Server Tricks & Tips
  • SSRS
  • SSIS
  • POWER BI
  • Python with ML
  • Microsoft Azure
  • HTML Color picker
Home » Check if column Exists or not in SQL Server Table

Check if column Exists or not in SQL Server Table

  • by Pradeep Raturi
  • SQL Interview Q & A, SQL Tricks & Tips

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 sp_rename

SQL Server sp_help

SQL Server sp_monitor

SQL Server sp_executesql

SQL Server Startup procedures Sp_Procoption

SQL Server Sp_helptext

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 30,803 total views,  76 views today

Share this:

  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on WhatsApp (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Related

Tags:Check if Column exists in tableChecking a column existence in a tableCOL_LENGTH function in SQLHow to check a column existence in SQL Server TableHow to check if a column exists in table ?How to check if column exists in SQL Server Table?IF EXISTS in SQLINFORMATION_SCHEMA.COLUMNSSQLSQL Column existence in SQLSQL DatabaseSQL Metadata functionsSQL Server TutorialSQL Server TutorialsSQL Server Tutorials By Pradeep RaturiSQL System catalog viewsSQL TutorialSQL TutorialsSys.columns Catalog view in SQLSys.columns in SQL

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

previousPrint Christmas tree in SQL Server
nextHow to get Ordinal Number in SQL
Generic selectors
Exact matches only
Search in title
Search in content

Page Views

  • 644,506 hits

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Translate this website..

Search for Tutorial

Generic selectors
Exact matches only
Search in title
Search in content
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Home
  • SQL Basics Concepts
  • SQL Advance Concepts
  • SQL Interview Q & A
  • SQL Server Tricks & Tips
  • SSRS-SQL Server Reporting Services
  • SSIS-SQL Server Integration Services
  • POWER BI
  • Python with Machine Learning
  • Microsoft Azure
  • HTML Color picker

SqlSkull. All Rights Reserved

 

Loading Comments...