May 14, 2021 at 9:23 pm
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?
May 14, 2021 at 9:55 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 15, 2021 at 1:44 am
how do i grab the table names and database names into a temp table to feed your query?
May 15, 2021 at 3:18 am
sys.all_objects will give you tables and views, and sys.all_columns will give you column names and type info etc.
May 15, 2021 at 4:36 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply