How to run the same query on all the databases on an instance?

Question

I have (for testing purposes) many dbs with the same schema (=same tables and columns basically) on a sql server 2008 r2 instance.

i would like a query like

SELECT COUNT(*) FROM CUSTOMERS

on all DBs on the instance. I would like to have as result 2 columns:

1 - the DB Name

2 - the value of COUNT(*)

Example:

DBName  //   COUNT (*)

TestDB1 // 4

MyDB  // 5

etc...

Note: i assume that CUSTOMERS table exists in all dbs (except master).

Eng

Get sp_ineachdb (articles on it here and here; download here). – Bricky

Answer 1

Try this one -

    SET NOCOUNT ON;
    
    IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
       DROP TABLE #temp
    
    CREATE TABLE #temp
    (
          [COUNT] INT
        , DB VARCHAR(50)
    )
    
    DECLARE @TableName NVARCHAR(50) 
    SELECT @TableName = '[dbo].[CUSTOMERS]'
    
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = STUFF((
        SELECT CHAR(13) + 'SELECT ' + QUOTENAME(name, '''') + ', COUNT(1) FROM ' + QUOTENAME(name) + '.' + QUOTENAME(@TableName)
        FROM sys.databases 
        WHERE OBJECT_ID(QUOTENAME(name) + '.' + QUOTENAME(@TableName)) IS NOT NULL
        FOR XML PATH(''), TYPE).value('text()[1]', 'NVARCHAR(MAX)'), 1, 1, '')
    
    INSERT INTO #temp (DB, [COUNT])              
    EXEC sys.sp_executesql @SQL
    
    SELECT * 
    FROM #temp t

Output (for example, in AdventureWorks) -

COUNT       DB
----------- --------------------------------------------------
19972       AdventureWorks2008R2
19975       AdventureWorks2012
19472       AdventureWorks2008R2_Live

Comity

Hi Devart, i am using this sql and works great. But is it possible to add a check if the user running this query has access to each database or not and if not, the query should still continue to run and return only the databases the user has access to. What I am after is whether the user has DBO role permission on each of the database and if not move onto next one.. please can you advise. – Montemontefiascone

@Charliface, this has stopped working for me with the latest revision. – Hallsy

The best method which I've ever seen. – Iroquoian

Answer 2

Straight forward query

EXECUTE sp_MSForEachDB 
        'USE ?; SELECT DB_NAME()AS DBName, 
        COUNT(1)AS [Count] FROM CUSTOMERS'

This query will show you what you want to see, but will also throw errors for each DB without a table called "CUSTOMERS". You will need to work out a logic to handle that.

Raj

Coppinger

+1 it is a very compact solution but i choose the other since your solution crates many result tables, while the other not – Eng

It probably worth mentioning that the sp_MSForEachDB is undocumented, unsupported, and has some known issues as discussed by Aaron Bertrand here sqlblog.org/2020/05/12/… – Fancy

Answer 3

How about something like this:

DECLARE c_db_names CURSOR FOR
SELECT name 
FROM sys.databases
WHERE name NOT IN('master', 'tempdb') --might need to exclude more dbs

OPEN c_db_names

FETCH c_db_names INTO @db_name

WHILE @@Fetch_Status = 0
BEGIN
  EXEC('
    INSERT INTO #report
    SELECT 
      ''' + @db_name + '''
      ,COUNT(*)
    FROM ' + @db_name + '..linkfile
  ')
  FETCH c_db_names INTO @db_name
END

CLOSE c_db_names
DEALLOCATE c_db_names

SELECT * FROM #report

Lys

This is a useful general snippet, I just replaced sys.databases with sys.tables for a very different purpose, using the same boilerplate. – Corkboard

You have forgotten in the script to declare [at]db_name, you need to add one line: DECLARE @db_name NVARCHAR (150), otherwise very useful script :-) – Santee

Where is the temp table delcared? – Perceptive

@Perceptive you're right it should be added, but it would just be a two column table with a name as VARCHAR and a count as INT. – Lys

Answer 4

declare @userdb_list table (name varchar(4000) not null);
-- fill the db list with custom subset
insert into @userdb_list
select name from sys.databases --can add where condition to filter db names

declare
@curr_userdb varchar(300),
@db_placeholder varchar(300),
@final_db_exec_query varchar(max),
@query varchar(max);
set @query = '' --  <add ur query here> 

set @db_placeholder = 'use {db}'; 
set @curr_userdb = (select min(name) from @userdb_list);

while @curr_userdb is not null
begin
set @final_db_exec_query = replace(@db_placeholder, '{db}', @curr_userdb +  '  ' + @query);
exec (@final_db_exec_query);
--print @final_db_exec_query
set @curr_userdb = (select min(name) from @userdb_list where name > @curr_userdb);
end
GO

Solution without cursor - clean and simple

Haeckel

Answer 5

How about a Devart's proposal for consolidated results variant, so you:

• Only have to focus on a query template;
• Won't need a temporary table;
• You can easily filter what databases to include and/or exclude.

Bear in mind columns names must be explicit. Since there is an union all, * are not supported

declare @template nvarchar(max) = '
select databaseName = ''{databaseName}''
, column1
, column2
from [{databaseName}].dbo.yourTargetTable r
';

declare @databasesNamesLikeExpr nvarchar(max) = '%';
declare @databasesNamesNotLikeExpr nvarchar(max) = 'undefined';

declare @query nvarchar(max) = '';

;with filteredDatabases_cte as
(
    SELECT d.name
    FROM sys.databases d 
    where d.name like @databasesNamesLikeExpr
    and d.name not like @databasesNamesNotLikeExpr
)

select @query = @query 
+ (case when @query = '' then '' else ' union all ' end)
+ replace(@template, '{databaseName}', db.name)
from filteredDatabases_cte db

EXEC sp_executesql @query

Tiltyard

Answer 6

Because I know that a question was just referred to here that asked a slightly different question... if you only want to execute on certain databases, those databases could be stored in some table. Here I stored in a temporary table.

CREATE TABLE #Databases (
DbName varchar(255))

INSERT INTO #Databases (DbName)
Values ('GIS_NewJersey'), ('GIS_Pennsylvania')

DECLARE @command varchar(1000)

SELECT @command = 'Use [' + DbName + '];
Update sde.SAP_Load
    SET FullAddress =  CONCAT_WS('','', HouseNumber, Street, City, Postal, RegionName)   
    Update sde.PREMISE
    SET FullAddress =  CONCAT_WS('', '', HouseNumber, Street, City, Postal, RegionName)
    Update sde.PREMISE_GEOCODE
    SET FullAddress =  CONCAT_WS('', '', HouseNumber, Street, City, Postal, RegionName)'
FROM #Databases

EXEC @command

Crapulous