Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
query12
Starting Member
6 Posts |
 Posted - 2012-10-14 : 09:07:51
|
| Hi,I'm trying to write a query like this:select *from(select t.name tablenamefrom sys.columns c join sys.tables t on c.object_id = t.object_idwhere c.name like 'column1') candidatetableswhere tablename.column2 = 3The 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-14 : 10:18:05
|
| 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. |
 |
|
|
query12
Starting Member
6 Posts |
Posted - 2012-10-14 : 10:26:19
|
| 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. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-14 : 10:41:06
|
| 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 |
|
|
|
query12
Starting Member
6 Posts |
|
|
|
|
|
|
|
combine table metadata and column values