TSQL Cross Apply determining which databases to query

57 Views Asked by At

I'm writing (in .NET) a login screen that allows the login to connect to a SQL Server. Once they've put in the server name and their credentials, it should then show a list of databases for them to connect to, but, the databases need to be of the right structure. This will be identified within each database by the existence of a ref.Config table, and a row in that table with appropriate values. There may be a whole bunch of other databases on the server for other purposes. I don't know at designtime.

Ideally what I'd like to do is something like this:

SELECT  m.name
FROM    MASTER.sys.databases m
        CROSS APPLY (SELECT *
                    FROM    {m.name}.INFORMATION_SCHEMA.TABLES t
                    WHERE   t.TABLE_SCHEMA = 'ref'
                    AND     t.TABLE_NAME 'Config') dbs
        CROSS APPLY (SELECT *
                    FROM    {m.name}.ref.Config c
                    WHERE   c.KeyName = 'DatabaseMagicNumber'
                    AND     c.KeyValue = '12345678') config
WHERE   HAS_DBACCESS(m.name) = 1
ORDER BY m.name

Where m.name gets substituted into the subqueries after evaluation (I know the above isn't valid SQL). Is there a way to do this, or do I have to run a query on each database? I am unable to have a stored procedure on the server at this point. Ideally I just want one SQL statement that will return the names of all databases that conform to the structure I expect.

0

There are 0 best solutions below