Solved

How to check if a column exists in a tablle

Posted on 2008-10-16
8
1,229 Views
Last Modified: 2012-05-05
I have a proc p_GetData that is invoked by many other procs,
 A temporary table is passed to the proc p_GetData as a paramater, @TableName  

I would like to know if a particular column exists in the temp table, can someone provide the sql for that

CREATE PROCEDURE p_GetData
    @TableName  varchar(100),
    @ServerName varchar(50),
    @UpdateFlag int,
    @SuccessFlag tinyint OUTPUT
AS



0
Comment
Question by:countrymeister
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:rpkhare
ID: 22734972
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'abcd'
0
 
LVL 1

Author Comment

by:countrymeister
ID: 22735081
rpkhare:

Doe this work in SQL Server 2000
0
 
LVL 8

Accepted Solution

by:
rpkhare earned 250 total points
ID: 22735097
I think it will.
0
 
LVL 1

Author Comment

by:countrymeister
ID: 22735710
Can you please let me know which is better
SELECT Count(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '@TableName' AND COLUMN_NAME = 'Action'

select object_name(id) as TableName, * from syscolumns where lower(name) like 'action'
and object_name(id) = '@TableName'
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 8

Expert Comment

by:rpkhare
ID: 22737860
The former seems to be more close to what you are desiring.
0
 
LVL 2

Expert Comment

by:Deepika_Rastogi
ID: 22739095
Hi

here in this query
SELECT Count(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '@TableName' AND COLUMN_NAME = 'Action'

you are putting @TableName within quotes which will produce error because @TableName is a variable that is containing the name of your table, so remove those quotes.

SELECT Count(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND COLUMN_NAME = 'Action'



0
 
LVL 1

Author Comment

by:countrymeister
ID: 22762207
For whatever reason it does not work, I would like to know if I create a global table in another  proc and then pass this to proc p_GetData , will it detect if the @TableName  has the column I am looking for
0
 
LVL 1

Author Closing Comment

by:countrymeister
ID: 31506903
This does not seem to work for global tables so I need to close this qt
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 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

19 Experts available now in Live!

Get 1:1 Help Now