A very frequent task among SQL developers is to check if any specific column exists in the database table or not. Based on the output developers perform various tasks. Here are couple of simple tricks which you can use to check if column exists in your database table or not.
Method 1
IF EXISTS(SELECT * FROM sys.columns
WHERE Name = N'columnName' AND OBJECT_ID = OBJECT_ID(N'tableName'))
BEGIN
PRINT 'Your Column Exists'
ENDÂ Â
For AdventureWorks sample database
IF EXISTS(SELECT * FROM sys.columns
WHERE Name = N'Name' AND OBJECT_ID = OBJECT_ID(N'[HumanResources].[Department]'))
BEGIN
PRINT 'Your Column Exists'
ENDÂ Â
Method 2
IF COL_LENGTH('table_name','column_name') IS NOT NULL
BEGIN
PRINT 'Your Column Exists'
END
For AdventureWorks sample database
IF COL_LENGTH('[HumanResources].[Department]','Name') IS NOT NULL
BEGIN
PRINT 'Your Column Exists'
END
Method 3
IF EXISTS(
SELECT TOP 1 *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'TableName'
AND [COLUMN_NAME] = 'ColumnName'
AND [TABLE_SCHEMA] = 'SchemaName')
BEGIN
PRINT 'Your Column Exists'
END
For AdventureWorks sample database
IF EXISTS(
SELECT TOP 1 *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'Department'
AND [COLUMN_NAME] = 'Name'
AND [TABLE_SCHEMA] = 'HumanResources')
BEGIN
PRINT 'Your Column Exists'
END
Let me know if you know any other method to find if Column Exists in SQL Server Table.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
19 Comments. Leave new
Hi pinal sir,
i gone through this article i got little doubt. here your used N in the where clause what is the use of “N” and why we use that in where clause earlier also i have seen this “N” in your posts please tell me sir.i tried this query without “N” in where clause it was also worked fine to me
Thanks
I prefer the information_schema approach, since this is ANSI. So Oracle and MySQL DBA’s also know the meaning of this statement….
This task is something I have had to do many times. An additional challenge is finding a column on any database on a server. This is the solution I came up with:
————————————————————————————
— Code to search for a string in all databases on a server —
— R. Shawn Myers 11/28/2011 —
————————————————————————————
CREATE TABLE #DB_NAMES
(
DBNAME varchar(255),
DATABASESIZE int,
REMARKS varchar(255)
)
INSERT #DB_NAMES exec sp_databases
CREATE TABLE #RESULTS
(
DATABASE_NAME varchar(255),
TABLE_NAME varchar(255),
COLUMN_NAME varchar(255)
)
Begin
Declare cur cursor for
SELECT DBNAME FROM #DB_NAMES ORDER BY 1
Declare @SQL varchar(255)
Declare @DB_NAME varchar(255)
Declare @SEARCH_STRING varchar(255)
open cur
fetch next from cur into @DB_NAME
Set @SEARCH_STRING = ‘%Your_Column_Name%’
WHILE @@FETCH_STATUS = 0
begin
set @SQL = ‘INSERT INTO #RESULTS (DATABASE_NAME, TABLE_NAME, COLUMN_NAME)select TABLE_CATALOG as DATABASE_NAME, TABLE_NAME, COLUMN_NAME FROM ‘ +@DB_NAME +’.INFORMATION_SCHEMA.COLUMNS WITH(NOLOCK) WHERE COLUMN_NAME LIKE ”’ + @SEARCH_STRING + ””
EXEC(@SQL)
print @sql
FETCH NEXT FROM cur INTO @DB_NAME
end
SELECT * FROM #RESULTS
DROP TABLE #DB_NAMES
DROP TABLE #RESULTS
CLOSE cur
DEALLOCATE cur
END;
GO
– Shawn
Hello,
we can also use Information_schema (correct me if I am wrong)
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ‘YOUR_COLUMN_NAME’
cheers
Very useful, it saved my day, Thanks Pinal…………..
Hi pinal sir, i got one doubt here this search query don’t works in some cases what i mean is print statements works fine but if you replace print statement with select statement like in my case below
IF EXISTS(select * from sys.columns where Name =N’securityuserFK’ and Object_id = Object_id(N'[dbo].[securityUser]’))
BEGIN
select USERID, UserName,FirstName,LastName
from securityUser where securityuserFK IS NOT NULL
END
When i run this i come up with this errro:
Invalid column name ‘securityuserFK’.
I think you can give me the valid reason for this.
Hi pinal sir, i got one doubt here this search query don’t works in some cases what i mean is print statements works fine but if you replace print statement with select statement like in my case below
IF EXISTS(select * from sys.columns where Name =N’securityuserFK’ and Object_id = Object_id(N’[dbo].[securityUser]‘))
BEGIN
select USERID, UserName,FirstName,LastName
from securityUser where securityuserFK IS NOT NULL
END
When i run this i come up with this errro:
Invalid column name ‘securityuserFK’.
Really, very useful information
A stored procedure to insert record into department table mantioned below,before inserting should check if departmenrt name already exist…..
database–Test
table-Department
column1—-DepartmentId bigint column2—Name varchar(50)
create database Test go use Test go create table Department ( DeptId bigint, DeptName varchar(50) ) go create proc insert_dept @id bigint, @name varchar(50) as Insert into Department(DeptId,DeptName)values (@id,@name) go insert_dept 101,’HR’ insert_dept 103,’Design’ insert_dept 104,’Development’ go IF COL_LENGTH(‘[Deptname].[Department]’,’Deptname’) IS NOT NULL BEGIN PRINT ‘Department Name already Exists’ END go insert_dept 104,’HR’
Execution Plan for Second Method is not Generated. Why?
Sir I am trying to create an trigger before insert into A table check the A table column value from the B table. Please can anyone help me out on this
Foreign key can also be used to maintain data integrity. What is the error you are facing? In trigger you can use inserted virtual table to know the value inserted and compare with other table.
Very useful for me. Thanks
IF COLUMNPROPERTY( OBJECT_ID(‘TableName’), ‘ColumnName’, ‘ColumnId’) IS NOT NULL
BEGIN
PRINT ‘Your Column Exists’
END
hello sir i requriment is if my sql database column in data found then column name and it’s all record display otherwise not display column name..
I am not sure your question.
hi how check column value,tablename exists all procedures? plz help me….
Will sp_depends help?