Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Solved
Check if column exists on SQL 2005
Posted on 2007-03-22
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_SCH
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
IF NOT EXISTS (SELECT TABLE_NAME,COLUMN_NAME
FROM master.dbo.INFORMATION_SCH
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
2
6 Comments
you were missing the closing )
also, it's master.INFORMATON_SCHEMA.C
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
also, it's master.INFORMATON_SCHEMA.C
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
thanks,
my first try was now only the select.
SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=N'tkey_wdReplac
but I got no results? I checked the table name and expected to get a list of the table fields.
Chris
my first try was now only the select.
SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=N'tkey_wdReplac
but I got no results? I checked the table name and expected to get a list of the table fields.
Chris
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?
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?
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses
Course of the Month6 days, 9 hours left to enroll
704 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.