If column exists query problem - SQL Server

Question

I have a query to add a column if it doesn't exist. It works the first time, but if I run it again, it fails, saying that the column exists?!?

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE_NAME' AND COLUMN_NAME = 'COLUMN_NAME')
BEGIN
    ALTER TABLE TABLE_NAME ADD COLUMN nchar(3) NULL;
END

And when I run the query against INFORMATION_SCHEMA.COLUMNS, it returns nothing.

I've also tried the

IF NOT EXISTS (SELECT * FROM SYS.COLUMNS WHERE NAME = N'COLUMN_NAME' AND OBJECT_ID = OBJECT_ID(N'TABLE_NAME'))

version, which exhibits the same behavior (it works once, and fails on the second run).

At what point do the sys tables get updated, and what is the fool proof way to test if a column exists?

Thanks, Sam

Answer 1

I believe it was a problem with USE due to our screwy database setup. The information_schema query was hitting master while the update targeted another database. D'oh!