Using SQL exists to check a criteria on all databases from a server

Question

I'm using VBA to display in an Excel sheet all customers filling some business related criteria.

The server I'm using uses 1 database per customer, I have no way around it, that's what it is. Thoses databases have the same structure though (same tables and same columns by table).

Through coding, I found a way to to this, which works, but I'd like/need to speed it up.

I first get all customers-related databases names through one query, and then execute a query on each result to decide if I should display it or not.

Since SQL treatments are considered to be faster than pretty much everything else, I think I could get my program running faster using a single query, with an "EXISTS" clause inside it, and display all results of this single query.

Here is roughly one thing I tried which might be close to what I need, but doesnt work as it is :

SELECT name FROM dbo.sysdatabases
WHERE (name LIKE 'DBC%')
AND EXISTS (SELECT TRUE FROM name.dbo.Purchases WHERE product_category = '012')

I just changed some parameters so it's not specific to my company, like what comes after LIKE and the condition after exists, the main point being that I need (do I ? ) to use name in my EXISTS clause to run through all relevant databases.

My problem is that SQL tells me that 'name.dbo.Purchases' is not a valid name (while, when I loop on a query so I can send a SQL query with a real name, say DBC01.dbo.Purchases, it works fine). How can I get name to be considered as what I'm already querying on, as opposed to "name" itself, in a single query ?

I'm not 100% sure that SELECT TRUE works fine but if I need to I'll take a simple field from my table, that's not a primary concern.

question from:https://stackoverflow.com/questions/65935907/using-sql-exists-to-check-a-criteria-on-all-databases-from-a-server

Answer 1

Waitting for answers