Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I need to add a specific column if it does not exist. I have something like the following, but it always returns false:
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTableName'
AND COLUMN_NAME = 'myColumnName')
How can I check if a column exists in a table of the SQL Server database?
–
–
–
–
IF EXISTS(SELECT 1 FROM sys.columns
WHERE Name = N'columnName'
AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
-- Column Exists
Martin Smith's version is shorter:
IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
-- Column Exists
–
–
–
–
IF COL_LENGTH('table_name','column_name') IS NULL
BEGIN
/* Column does not exist or caller does not have permission to view the object */
The point about permissions on viewing metadata applies to all answers, not just this one.
Note that the first parameter table name to COL_LENGTH can be in one, two, or three part name format as required.
An example referencing a table in a different database is:
COL_LENGTH('AdventureWorks2012.HumanResources.Department','ModifiedDate')
One difference with this answer, compared to using the metadata views, is that metadata functions, such as COL_LENGTH, always only return data about committed changes, irrespective of the isolation level in effect.
–
–
–
–
–
–
–
–
–
–
For the people who are checking the column existence before dropping it.
From SQL Server 2016 you can use new DIE (Drop If Exists) statements instead of big IF wrappers
ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name
–
–
–
–
–
You can use the information schema system views to find out pretty much anything about the tables you're interested in:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableName'
ORDER BY ORDINAL_POSITION
You can also interrogate views, stored procedures and pretty much anything about the database using the Information_schema views.
–
Try something like:
CREATE FUNCTION ColumnExists(@TableName varchar(100), @ColumnName varchar(100))
RETURNS varchar(1) AS
BEGIN
DECLARE @Result varchar(1);
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
BEGIN
SET @Result = 'T'
BEGIN
SET @Result = 'F'
RETURN @Result;
GRANT EXECUTE ON [ColumnExists] TO [whoever]
Then use it like this:
IF ColumnExists('xxx', 'yyyy') = 'F'
BEGIN
ALTER TABLE xxx
ADD yyyyy varChar(10) NOT NULL
It should work on both SQL Server 2000 and SQL Server 2005. I am not sure about SQL Server 2008, but I don't see why not.
First check if the table/column(id/name) combination exists in dbo.syscolumns (an internal SQL Server table that contains field definitions), and if not issue the appropriate ALTER TABLE query to add it. For example:
IF NOT EXISTS ( SELECT *
FROM syscolumns
WHERE id = OBJECT_ID('Client')
AND name = 'Name' )
ALTER TABLE Client
ADD Name VARCHAR(64) NULL
A good friend and colleague of mine showed me how you can also use an IF block with SQL functions OBJECT_ID and COLUMNPROPERTY in SQL Server 2005 and later to check for a column. You can use something similar to the following:
You can see for yourself here:
IF (OBJECT_ID(N'[dbo].[myTable]') IS NOT NULL AND
COLUMNPROPERTY( OBJECT_ID(N'[dbo].[myTable]'), 'ThisColumnDoesNotExist', 'ColumnId') IS NULL)
BEGIN
SELECT 'Column does not exist -- You can add TSQL to add the column here'
–
select 1
from information_schema.columns columns
where columns.table_catalog = 'myDatabase'
and columns.table_schema = 'mySchema'
and columns.table_name = 'myTable'
and columns.column_name = @myColumn
begin
exec('alter table myDatabase.mySchema.myTable add'
+' ['+@myColumn+'] bigint null')
–
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_name'
AND column_name = 'column_name'
BEGIN
SELECT COLUMNS.*
FROM INFORMATION_SCHEMA.COLUMNS COLUMNS,
INFORMATION_SCHEMA.TABLES TABLES
WHERE COLUMNS.TABLE_NAME = TABLES.TABLE_NAME
AND Upper(COLUMNS.COLUMN_NAME) = Upper('column_name')
–
I needed something similar for SQL Server 2000 and, as Mitch points out, this only works in SQL Server 2005 or later.
This is what worked for me in the end:
if exists (
select *
sysobjects, syscolumns
where
sysobjects.id = syscolumns.id
and sysobjects.name = 'table'
and syscolumns.name = 'column')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'TableName'
AND table_schema = 'SchemaName'
AND column_name = 'ColumnName') BEGIN
ALTER TABLE [SchemaName].[TableName] ADD [ColumnName] int(1) NOT NULL default '0';
One of the simplest and understandable solutions is:
IF COL_LENGTH('Table_Name','Column_Name') IS NULL
BEGIN
-- Column Not Exists, implement your logic
BEGIN
-- Column Exists, implement your logic
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = '<table_name>'
and COLUMN_NAME = '<column_name>'
) begin
print 'Column you have specified exists'
end else begin
print 'Column does not exist'
A temporary table version of the accepted answer:
if (exists(select 1
from tempdb.sys.columns
where Name = 'columnName'
and Object_ID = object_id('tempdb..#tableName')))
begin
–
–
There are several ways to check the existence of a column.
I would strongly recommend to use INFORMATION_SCHEMA.COLUMNS as it is created in order to communicate with user.
Consider following tables:
sys.objects
sys.columns
and even some other access methods available to check system catalog.
Also, no need to use SELECT *, simply test it by NULL value
IF EXISTS(
SELECT NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'myTableName'
AND COLUMN_NAME = 'myColumnName'
–
Wheat's answer is good, but it assumes you do not have any identical table name / column name pairs in any schema or database. To make it safe for that condition, use this...
select *
from Information_Schema.Columns
where Table_Catalog = 'DatabaseName'
and Table_Schema = 'SchemaName'
and Table_Name = 'TableName'
and Column_Name = 'ColumnName'
Do something if the column does not exist:
BEGIN
IF (COL_LENGTH('[dbo].[Table]', 'Column ') IS NULL)
BEGIN
// Do something
Do something if the column does exist:
BEGIN
IF (COL_LENGTH('[dbo].[Table]', 'Column ') IS NOT NULL)
BEGIN
// Do something
Here is a simple script I use to manage addition of columns in the database:
IF NOT EXISTS (
SELECT *
FROM sys.Columns
WHERE Name = N'QbId'
AND Object_Id = Object_Id(N'Driver')
BEGIN
ALTER TABLE Driver ADD QbId NVARCHAR(20) NULL
BEGIN
PRINT 'QbId is already added on Driver'
In this example, the Name is the ColumnName to be added and Object_Id is the TableName
Another contribution is the following sample that adds the column if it does not exist.
USE [Northwind]
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Categories'
AND COLUMN_NAME = 'Note')
BEGIN
ALTER TABLE Categories ADD Note NVARCHAR(800) NULL
The below query can be used to check whether searched column exists or not in the table. We can take a decision based on the searched result, also as shown below.
IF EXISTS (SELECT 'Y' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = <YourTableName> AND COLUMN_NAME = <YourColumnName>)
BEGIN
SELECT 'Column Already Exists.'
BEGIN
ALTER TABLE <YourTableName> ADD <YourColumnName> <DataType>[Size]
–
You can check multiple columns in SQLDB at once and return a string as status to check if columns exist:
IF EXISTS
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table Name'
AND(COLUMN_NAME = 'column 1'
or COLUMN_NAME = 'column 2'
or COLUMN_NAME = 'column 3'
or COLUMN_NAME = 'column 4')
SELECT 'Column exists in table' AS[Status];
SELECT 'Column does not exist in table' AS[Status];
Execute the below query to check if the column exists in the given table:
IF(SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName') IS NOT NULL
PRINT 'Column Exists in the given table';
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'Database Name'
and TABLE_SCHEMA = 'Schema Name'
and TABLE_NAME = 'Table Name'
and COLUMN_NAME = 'Column Name'
and DATA_TYPE = 'Column Type') -- Where statement lines can be deleted.
BEGIN
-- Column exists in table
ELSE BEGIN
-- Column does not exist in table
