0

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Mark Roworth
  • 409
  • 2
  • 15
  • 2
    Does this answer your question? [How do I list all tables in all databases in SQL Server in a single result set?](https://stackoverflow.com/questions/2875768/how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set) – GSerg Jul 07 '21 at 08:33
  • Does this answer your question? [Microsoft SQL Server: Query to see list of all databases, and their corresponding tables, and columns](https://stackoverflow.com/q/43945327/11683) – GSerg Jul 07 '21 at 08:34
  • Yes, I think that's a goer - thank you. Getting a collation error in it (I've used a different collation in one database for testing with collation), but I can resolve that. Thank you. – Mark Roworth Jul 07 '21 at 08:46
  • 1
    Careful - use of [information_schema.tables.table_schema](https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/tables-transact-sql?view=sql-server-ver15) is discouraged – SMor Jul 07 '21 at 12:17
  • I'd also make sure you are very careful about protecting against SQL injection since it seems like you are planning to go the dynamic SQL route. – Anthony Hancock Jul 07 '21 at 13:33

0 Answers0