Checking if a column exists before creating it.

I want to add an extra column to a database table if the column in the table does not yet exists. Basically I am using the approach below. My actual alter statement is not shown. Is this approach ok, or are there any shortcomings. Is there a way to make it better?


IF COL_LENGTH('Customers','lastPurchaseDate') IS NOT NULL
BEGIN
PRINT 'ADD ALTER STATEMENT HERE' -- I will add my alter statement here to create column lastPurchaseDate
END
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
INFORMATION_SCHEMA.COLUMNS is ideally what you need to check...

checking IF COL_LENGTH('Customers','lastPurchaseDate') IS NULL

will work, but essentially checking select [name], object_name(id), length from sys.syscolumns

if not exists (select null from sys.syscolumns where [name] = 'your column name' and object_name(id) = 'your table name')  
begin 
   alter table [your table name] add [your column name] datatype (length)
end      

Open in new window

OR
if (select length from sys.syscolumns where [name] = 'your column name' and object_name(id) = 'your table name') is NULL  
begin 
   alter table [your table name] add [your column name] datatype (length)
end      

Open in new window


Can be a few subtleties like checking for schema and variations. Thats why information_schema.columns is also the safer place to check.
0
 
Ryan ChongCommented:
shouldn't it be as:

IF COL_LENGTH('Customers','lastPurchaseDate') IS NULL
BEGIN
PRINT 'ADD ALTER STATEMENT HERE' -- I will add my alter statement here to create column lastPurchaseDate
END

if that column does not exist yet?

or use this:

IF NOT EXISTS(SELECT *
          FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = 'Customers'
                 AND COLUMN_NAME = 'lastPurchaseDate')
BEGIN
PRINT 'ADD ALTER STATEMENT HERE' -- I will add my alter statement here to create column lastPurchaseDate
END

Open in new window

0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Your method is fine.  It's not typically done that way, but it looks like it should work.

Do not use INFORMATION_SCHEMA views.  They are incomplete, not fully accurate and I've found them to be slow and to cause blocking.

Do not use the ancient view "syscolumns" (nor, similarly, sysobjects, etc.).

The method I use, which is typical, is to reference sys.columns:

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Customers') AND name = 'lastPurchaseDate')
BEGIN
    --ALTER TABLE here
END /*IF*/
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Mark WillsTopic AdvisorCommented:
Scott,

No need for your derogatory overtones. Rain it in please...

and it is a current system view for what used to be a table. yes it does have a newer name. but hardly an "ancient view" look inside a few of the MS procs, even they use some of the "ancient"

And how are information_schema incomplete for checking the existence of a column or not.
0
 
Scott PletcherSenior DBACommented:
With the release of SQL 2005, i.e. more than twelve years ago, MS stated that the sysviews were meant for backward compatibility only, and that the new sys.views should be used for all new code.

Are you saying that, despite all that, as experts, we should recommend that new code be written using those 12-year-old views?  Why?  Why not just say, "yeah, that's true, you should use the "new" system views in your code."

Btw, I don't think I'm alone in considering a twelve-year-old method "ancient" in technology terms.  Anyone want a 12-year-old cell phone? :-):-)
0
 
brgdotnetcontractorAuthor Commented:
Thanks Everyone,

Hi Scott. What are the consequences if any of leaving out the "dbo." referencing the table name.

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Customers') AND name = 'lastPurchaseDate')
BEGIN
    --ALTER TABLE here
END /*IF*/
0
 
Scott PletcherSenior DBACommented:
I practical terms, none really.  You can almost certainly leave it out.

It's just my habit, because:

1) it can add some efficiency: SQL has to determine the default schema if you don't specify it.  If the code happens to be running under a different user, the default schema might not be dbo.  It's easy to forget, but the truly unique object name is "schema.object", not just "object", so SQL must determine the schema name and use it.

2) we do deliberately have a very few power users who are allowed to create tables of their own, which are in their own schemas.  I'm just making absolutely sure I don't get the wrong table.

For just an object_id check, it's extremely unlikely to matter, you can just leave the schema out.

It's much more critical to include the schemas in an actual SELECT / UPDATE / etc. statement.  There it can dramatically affect performance and even permissions.
0
 
Mark WillsTopic AdvisorCommented:
Scott, that's why SQL Server continue to support it in each new release.

And you have NOT explained your information_schema comment.

In which case I refer to the first two posts agreeing that

 IF COL_LENGTH('Customers','lastPurchaseDate') IS NULL

will achieve the requirement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.