PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
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
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed.
Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 21 hours left to enroll
626 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.