SQL query to query all databases that contain a specific table then query that table.

• acrutchley

  SSCrazy

  Points: 2117

  More actions

  September 10, 2014 at 1:40 pm

  #293159

  Hi All,

  I really need some advice on the following task... I think it should be simple, but not for the lack of trying I am going round in circles!

  Overview:

  I have a requirement to list all databases on an instance that have a particular table, then be able to query each table for each database name returned and insert that data into another database.

  E.g.

  I have 60 databases on a server instance, 30 of them have a table called "activity" (example only), the table contains logon information for each application they support. I need to find all databases with the "activity" table, query each one and collate the results into a central admin database table.

  I have tried a cursor so far using the following code to set up the databases to query followed by a simple select in the "to do" part.... (I can add full cursor to the post tomorrow when I am back in work):

  SELECT name

  FROM sys.databases

  WHERE CASE

  WHEN state_desc = 'ONLINE'

  THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[activity]', 'U')

  END IS NOT NULL

  On its own the code returns the right database names each time, however, when I add it to the cursor the overall results contain either results for all databases listed from the query above (what I want), or just one database result set with a @@fetch_status code of -2 (The row fetched is missing). If I bash away at the F5 key it will eventually return all the results. But this is usally a one in 8 - 12 runs!

  Ideally I don't want to use a cursor but not sure how to get around them in this scenario.

  I have also looked at sp_ms_foreach but I would rather have more control.

  Any help is greatfully received.

  Adam