I have a DataBase with around +100 tables, like half of tables have column A & column B.
My question is, Can I query all tables that have this columns with a specific values e.g.
SELECT * FROM DATABASE
WHERE
EACHTABLE HAS COLUMN A = 21 //only if table has columns and then values
AND
COLUMN B = 13
I am not sure how exact I will do it, nothing is coming up on google either
You can use the undocumented MS stored procedure sp_MSforeachtable, if you fancy living life recklessly:
create table T1 (
ColumnA int not null,
ColumnB int not null
)
go
create table T2 (
ColumnA int not null,
Column2 int not null
)
go
create table T3 (
Column1 int not null,
ColumnB int not null
)
go
create table T4 (
ColumnA int not null,
ColumnB int not null
)
go
insert into T1 values (1,2);
insert into T2 values (3,4);
insert into T3 values (5,6);
insert into T4 values (7,8);
go
create table #Results (TableName sysname,ColumnA int,ColumnB int)
exec sp_MSforeachtable 'insert into #Results select ''?'',ColumnA,ColumnB from ?',
@whereand = ' and syso.object_id in (select object_id from sys.columns where name=''ColumnA'') and syso.object_id in (select object_id from sys.columns where name=''ColumnB'')'
select * from #Results
drop table #Results
Result:
TableName ColumnA ColumnB
------------------------------------- ----------- -----------
[dbo].[T1] 1 2
[dbo].[T4] 7 8
By default, sp_MSforeachtable will, as its name implies, perform the same task for each table in the database. However, one optional parameter to this procedure, called @Whereand, can be used to modify the WHERE clause of the internal query that enumerates the tables in the database. It helps to know that this internal query has already established two aliases to some of the system views. o is an alias for sysobjects (the legacy system view). syso is an alias for sys.all_objects (a more modern system view).
Once sp_MSforeachtable has decided which tables to run against, it will execute the query given to it as its first parameter. But, it will replace ? with the schema and table name (? is the default replacement character. This can be changed as needed)
In this case, I chose to create a temp table, then have each selected table store its results into this temp table, and after sp_MSforeachtable has finished running, to select the combined results out with no further processing.
There is a similar (and similarly undocumented) procedure called sp_MSforeachdb which will access each user database on the server. These can even be combined (although you have to be careful with doubling up ' quote characters twice, at times). However, there's no equivalent sp_MSforeachcolumn.
Try this:
select t.name from sys.objects t inner join sys.columns c
on t.name=OBJECT_NAME(c.object_id)
where t.type='U'
and c.name in('col1','col2')
group by t.name
having COUNT(*) = 2
order by 1
Then you just loop through all the tables and fine the values for these columns.
Like
Declare @out TABLE(tblname varchar(100))
if exists(select * from tbl1 where col1='21' and col2='22')
BEGIN
INSERT INTO @out
select tbl1
END
You can try like this using dynamic query.
select 'select * from '+table_name+ ' where'+column_name+'=21'
from information_schema.columns where column_name = 'A'
I suggest to use two steps: First, find out all tables in your database that have these two columns and use it for a temporal derived table. For I am not an expert in SQL-Server 2008 I recommend to have a look at the whitepages.
The expression might look like this:
SELECT tablename
FROM information_schema.tables sdbt
WHERE "column a" IN
(SELECT columns
FROM information_schema.columns col
WHERE col.tablename = sdbt.tablename)
Second, use a expresssion to filter the results according to your demanded values.
This command should do the trick in one go, only for column A, amend accordingly to include any other columns you need:
exec sp_MSforeachtable
@command1=N'SELECT * FROM ? WHERE A = 21',
@whereand=' and o.name IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''A'') '
