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,
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.
v_count NUMBER(1) := 0;
WHERE table_name = ‘TEST’
AND column_name = ‘TEST_DATE’;
WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE ‘ALTER TABLE TEST ADD TEST_DATE DATE’;
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’)
ALTER TABLE TEST ADD TEST_DATE DATETIME
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”
Sorry, the comment form is closed at this time.