Checking if a column exists before creating it.

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

Select allOpen in new window

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.

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? :-):-)

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*/

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.

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.