CODEWITHSUNDEEP
sql-serversql-server-2008-r2multiple-databases
UnDiUdin
UnDiUdin

Reputation: 15384

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

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).

Upvotes: 44

Views: 105176

Answers (6)

Julio Nobre
Julio Nobre

Reputation: 4395

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

Upvotes: 0

aquesh
aquesh

Reputation: 9

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

Upvotes: 0

CMGeek
CMGeek

Reputation: 134

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

Upvotes: -2

Devart
Devart

Reputation: 121912

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

Upvotes: 52

Dave Sexton
Dave Sexton

Reputation: 11188

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

Upvotes: 8

Raj
Raj

Reputation: 10843

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

Upvotes: 42

Related Questions