Solved

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

Posted on 2010-09-13
11
318 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

831 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