Solved

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

Posted on 2010-09-13
11
319 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 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