CODEWITHSUNDEEP
sqljoin
JohnZ
JohnZ

Reputation: 482

See data from multiple tables depending on column name

What I am trying to achieve is to get information from multiple tables based upon a column name and then narrow down from that result.

Something to this effect: 

select 
    top 3 * 
from 
    (Select TABLE_NAME From INFORMATION_SCHEMA.COLUMNS Where column_name = 'modify_id')
where
    MODIFY_ID = 'MULVEYR' or 
    MODIFY_ID = 'MULVEYR2' and 
    MODIFY_DATE > '12/31/2013'

And if possible, not to return anything if it returns 0 results for that table. The number of tables that have that column is 188, which is why I need to get something automated and not write out or check each manually.

Ideal output : Out of the 188 tables, lets say only 50 of them have the information I need (where clause), then I would only see the top 3 lines of those 50 tables and not the blank results for the remaining 133

Upvotes: 0

Views: 49

Answers (2)

Bill
Bill

Reputation: 4585

If I read you right, you are trying to search all of your tables for the one's that have a particular column, and then return a few records from each of those tables. Here is a solution, but I warn you, it uses dynamic sql and cursors, so it is very slow.

Declare cur_TableList Cursor
  For
    Select TABLE_SCHEMA, TABLE_NAME
    from INFORMATION_SCHEMA.COLUMNS
    Where COLUMN_NAME='modify_id';

Open cur_TableList
Fetch Next From cur_TableList
  Into @tblSchema, @tblName;

While @@FETCH_STATUS = 0
BEGIN
    Set @sqlStmt = N'Select Top 3 * From ' + @tblSchema + '.' + @tblName;
    Set @sqlStmt = @sqlStmt + ' where MODIFY_ID = ''MULVEYR'' or ';
    Set @sqlStmt = @sqlStmt + 'MODIFY_ID = ''MULVEYR2'' and ';
    Set @sqlStmt = @sqlStmt + 'MODIFY_DATE > ''12/31/2013'' ';

    Print @tblName;

    EXECUTE sp_executesql @sqlStmt ;

    Fetch Next From cur_TableList
      Into @tblSchema, @tblName;
END

CLOSE cur_TableList;
DEALLOCATE cur_TableList;

Upvotes: 1

JDGuide
JDGuide

Reputation: 6525

Try this query :-

select top 3 x.* from 
(
select * from INFORMATION_SCHEMA.COLUMNS where column_name = 'modify_id'    
)x

Upvotes: 0

Related Questions