• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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

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
RIAS
Asked:
RIAS
  • 6
  • 5
1 Solution
 
chapmandewCommented:
select * from information_schema.columns
where table_name = 'tablename' and column_name = 'columnname'
0
 
RIASAuthor Commented:
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
 
chapmandewCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RIASAuthor Commented:
Cheers mate will try and get back
0
 
RIASAuthor Commented:
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
 
chapmandewCommented:
I am not sure I follow.  You want to see ALL tables, and an indication if the table has that field or not?
0
 
RIASAuthor Commented:
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
 
chapmandewCommented:
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
 
RIASAuthor Commented:
Hi,
Got this error :<

The multi-part identifier "T.Table_Name" could not be bound.
0
 
chapmandewCommented:
show me your query
0
 
RIASAuthor Commented:
Cheers mate!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now