Solved

Check if column exists on SQL 2005

Posted on 2007-03-22
6
12,234 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 18769564
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
ID: 18769580
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18769636
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

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

Expert Comment

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

0
 
LVL 143

Expert Comment

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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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, show how to extract information from SQL Server on Database, Connection and Server properties

860 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