Systems Engineering and RDBMS

Checking for the existence of a column in a table

Posted by decipherinfosys on August 30, 2007

As DBAs or database developers, when we need to write up scripts that need to be rolled out from the development environment to QA or production environment, it is always a good idea to have non-destructive DDL scripts in place. One such scenario is an addition of a column to an existing table. We want to add a new column but first we want to make sure that the column does not exist in the table rather than getting an error at the time of running the script.

In this blog post, we will show you how can we check for the existence of a column before adding it to the table using a small code snippet for Oracle and SQL Server. Let us first create a table. Table creation syntax is in Oracle. Please make appropriate changes, if you want to run it on SQL Server.

CREATE TABLE TEST
(
TEST_ID   NUMBER(9) PRIMARY KEY,
TEST_NAME VARCHAR(50)
)
/

We want to add new column TEST_DATE with date data type but we want to do it in non-destructive way so even if somebody runs the script more than once, it does not give any error. Following is the code snippet for Oracle.

DECLARE
v_count NUMBER(1) := 0;

BEGIN
SELECT 1
INTO v_count
FROM USER_TAB_COLUMNS
WHERE table_name = ‘TEST’
AND column_name = ‘TEST_DATE’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE ‘ALTER TABLE TEST ADD TEST_DATE DATE’;
END;
/

In the above PL/SQL block, we are checking existence of column in USER_TAB_COLUMNS view. If we get exception then we go ahead and create the column. This way same script can be run multiple times without running into any error.

Syntax for SQL Server is little simpler. We don’t need to create the local variable. Following is the command in SQL Server. Please create the table in SQL Server before running the script.

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘TEST’ AND COLUMN_NAME = ‘TEST_DATE’)
BEGIN
ALTER TABLE TEST ADD TEST_DATE DATETIME
END
GO

For SQL Server we are going against the information_schema.column view to check the existence of the column. SQL Server supports ‘IF EXISTS’ and ‘IF NOT EXISTS’ syntax which helps in making the existence check logic easy to implement. Information_Schema views return the information about the objects which current user has permissions on. ‘Columns’ view contains one row for each column in the database which user has access to.  We can also check against the system tables (sys.objects, sys.columns etc.) but it is not advisable and Microsoft recommends using Information_Schema views for meta-data information.

One Response to “Checking for the existence of a column in a table”

  1. […] by decipherinfosys on September 13th, 2007 In one of our previous post, we had covered how to check for column existence in Oracle and SQL Server.  In this post, we will […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: