SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 combine table metadata and column values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

query12
Starting Member

6 Posts

Posted - 10/14/2012 :  09:07:51  Show Profile  Reply with Quote
Hi,

I'm trying to write a query like this:

select *
from
(select t.name tablename
from sys.columns c join sys.tables t on c.object_id = t.object_id
where c.name like 'column1'
) candidatetables
where tablename.column2 = 3

The problem is in the underlined filter. I get this error "The multi-part identifier "tablename.column2" could not be bound.".

This query should get the tables that have the column "column1" and for which the value of the column named "column2", that is sure to exists in all the tables, equals 3. Is it possible to write the last filter in a different manner in order to accomplish this?

Thanks.

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  10:18:05  Show Profile  Reply with Quote
I didn't quite follow your requirement, but, if you run the inner query by itself, you will see that it returns just one column with the column name being tablename and all rows (if any) having the value column2.

Are you trying to do the following? If this is not it, can you describe with some examples?

a) Look through all the tables in the database and pick out every table that has a column named "column1"

b) Look through all such tables and if there are any rows in which the column "column1" has the string "column2", then select such rows.
Go to Top of Page

query12
Starting Member

6 Posts

Posted - 10/14/2012 :  10:26:19  Show Profile  Reply with Quote
Say I have tables T1(column1, column2), T2(column1, column2), T3(column1, column2), T4(column4, column2).
Furthermore, assume T1.column2 = 3, T2.column2 = 1000, T3.column2 = 3.
After a) I get T1, T2 and T3, as they all have column1 in their schema.
But in the next step, I want to obtain tables T1 and T3, because the value of the field column2 for these tables is 3.

Hope this is more clear.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  10:41:06  Show Profile  Reply with Quote
Take a look at this blog to see if that will do what you described: http://beyondrelational.com/modules/2/blogs/78/posts/11138/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspx
Go to Top of Page

query12
Starting Member

6 Posts

Posted - 10/14/2012 :  12:14:03  Show Profile  Reply with Quote
A very good response here: http://stackoverflow.com/questions/12882616/t-sql-combine-table-metadata-and-column-values .
I will check your link as well. Thank you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000