-2

I have multiple column with same name from multiple table, table_1 has column X, table_2 has column x and more. How to select query for X column from all table without union. If use union, i must write every table name on query statement, i don't do this. I want do like :

Select X from (All table with containing X) where X value (not X name) = 'ABC'.

I use Microsoft SQL. Is it possible ? thanks

Elias Hossain
  • 4,410
  • 1
  • 19
  • 33
user3440349
  • 1
  • 1

2 Answers2

1

You can do that using dynamic SQL and build the query this way:

declare @sql as nvarchar(max) = N''
declare @columnname as nvarchar(100) = N'X'
declare @columnvalue as nvarchar(100) = N'ABC'

select @sql = @sql + N' UNION ALL SELECT ' + QUOTENAME(c.name) + N' FROM '
    + QUOTENAME(s.name) + N'.'
    + QUOTENAME(t.name)
    + N' WHERE ' + QUOTENAME(c.name) + N' = ''' + @columnvalue +  ''''
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id
where c.name = @columnname

set @sql = STUFF(@sql, 1, 10, N'')

exec sp_executesql @sql
Szymon
  • 42,577
  • 16
  • 96
  • 114
-2

Try the following it will be useful to you.

   Select a.x,b.x
   from table_name a,table_name1 b
   where a.x = b.x
   and a.x = 'ABC';
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
Tamil Thomas
  • 1
  • 2