Solved

Find table exists in database and if yes then check if field exists in the table

Posted on 2010-09-13
11
321 Views
Last Modified: 2012-05-10
Hi,
Need to combine two queries in one .
example:      SQL = "SELECT Name AS TABLE_NAME FROM sysobjects WHERE type = 'U' ORDER BY Name"
             if yes then check if 'refno' field exists in the table

Cheers
0
Comment
Question by:RIAS
[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
  • 6
  • 5
11 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 33661850
select * from information_schema.columns
where table_name = 'tablename' and column_name = 'columnname'
0
 

Author Comment

by:RIAS
ID: 33661905
Hi,
Thanks for the reply.But need to get all tables from sysobjects and check if field'refno' exists in them .At the moment I have query

sql= "SELECT Name AS TABLE_NAME FROM sysobjects WHERE type = 'U' ORDER BY Name"

strSql = " Select COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = '" & Table & "'" & " AND COLUMN_NAME ='" & Field & "'"
 
Need to combine in 1 query ?
Cheers
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 33661921
Ah, in that case just take out your table_name criteria:

strSql = " Select Table_name, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS Where COLUMN_NAME ='" & Field & "'"
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:RIAS
ID: 33661957
Cheers mate will try and get back
0
 

Author Comment

by:RIAS
ID: 33662386
Hi,
The query returns only tables with field 'refno' whereas I need to know both the cases.One with refno field and one without.
Cheers
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 33662405
I am not sure I follow.  You want to see ALL tables, and an indication if the table has that field or not?
0
 

Author Comment

by:RIAS
ID: 33662440
yes correct.
But  the query return all the table which have 'refno' field in them  and does not give the table names with no refno field in them
 
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 total points
ID: 33662461
SELECT Table_Name,
CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C WHERE T.Table_Name = c.Table_name AND
c.Column_name = 'RefNo') THEN 'EXISTS' ELSE 'DOES NOT EXIST' END
FROM INFORMATION_SCHEMA.Tables T
0
 

Author Comment

by:RIAS
ID: 33662528
Hi,
Got this error :<

The multi-part identifier "T.Table_Name" could not be bound.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 33662539
show me your query
0
 

Author Closing Comment

by:RIAS
ID: 33662732
Cheers mate!!!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

615 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