Solved

Check if column exists on SQL 2005

Posted on 2007-03-22
6
12,230 Views
Last Modified: 2009-03-12
I found this script to check if a column exist in a table and to create the col if not exisiting:

IF NOT EXISTS (SELECT TABLE_NAME,COLUMN_NAME
                         FROM master.dbo.INFORMATION_SCHEMA_COLUMNS
                         WHERE TABLE_NAME=N'myTable' AND COLUMN_NAME = N'myNewCol'
ALTER TABLE myTable
ADD myNewCol int NOT NULL

but it is not working on SQL 2005 Standard.
Why
0
Comment
Question by:chriswies
  • 3
  • 2
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
you were missing the closing )
also, it's master.INFORMATON_SCHEMA.Columns, and not master.dbo.information_schema_columns
also, you should NOT specify master.dbo for the information_schema views:

IF NOT EXISTS (SELECT TABLE_NAME,COLUMN_NAME
                         FROM INFORMATION_SCHEMA.COLUMNS
                         WHERE TABLE_NAME=N'myTable' AND COLUMN_NAME = N'myNewCol' )
BEGIN
ALTER TABLE myTable
ADD myNewCol int NOT NULL
END
0
 

Author Comment

by:chriswies
Comment Utility
thanks,
my first try was now only the select.

SELECT TABLE_NAME,COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME=N'tkey_wdReplacements'

but I got no results? I checked the table name and expected to get a list of the table fields.
Chris
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
are you in the database itself (ie the context).
the information_schema views refer to the currently connected database...

does the full query:
SELECT DISTINCT TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS

return your table(s) of the database?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:chriswies
Comment Utility
OK, I got it. I was in Master, not in my db.
Thanks
Chris
0
 

Expert Comment

by:scollege
Comment Utility
Doesn't work for me either, and I am most certainly in the correct database.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
scollege: you shall open your own question if this "solution" does not help you.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now