sql help querying across databases

  • I am trying to get the multiple tables from multiple dbs together in a view where database names like ( '%agriculture%' or '%culture%' or '%agri%')and table names like ('%reference%','%ref%','%matching%','%match%') , all of these tables has common fields called storeid and storenumber. My final result should have a databasename,tablename,storeid,storename.


    any help with this sql please?


  • If the database names and table names are static and not going to change (ie no new ones coming in and none going away), then I'd recommend hard-coding it all in.

    Now, if the table names and database names are going to be dynamic (ie adding and removing over time), you are going to need to do some sort of dynamic SQL.  If you go with the dynamic SQL route, I would ALSO recommend looking at the sys.columns table to make sure that the storeid and storename are valid columns.

    The way I'd do it is have a table variable that stores the database, the table, and a row number.  Then your TSQL will looks something like:

    SELECT CASE WHEN rownumber = 1 then 'SELECT ' + databasename + ', ' +  tablename + ', storeID, storename FROM ' + databasename + '.dbo.' + tablename  ELSE 'UNION ALL SELECT ' + databasename + ', ' +  tablename + ', storeID, storename FROM ' + databasename + '.dbo.' + tablename END AS tsql
    FROM #tmpTable

    Now you have a column that has all of your TSQL to run.  Put that column into a variable (STUFF should be able to handle that) and run the query.  NOTE - I would PRINT it first to make sure it looks like what you expect.

  • how do i grab the table names and database names into a temp table to feed your query?

  • sys.all_objects will give you tables and views, and sys.all_columns will give you column names and type info etc.

    • This reply was modified 1 month ago by  pietlinden.
  • What is the purpose of this view - and where will this view live?  Another option is to create a view in each database that provides the information you are looking for, and then use a UNION ALL query across all databases.

    The UNION ALL query would built using dynamic SQL that checks each database for the existence of that view, which would be done using a cursor and sys.databases.  If the view exists, generate a UNION ALL Select ... From {database}.{schema}.{view} in your dynamic string.

    The dynamic SQL then creates the view - and you can schedule that view to be recreated every day.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply