如何在实例中对所有数据库运行相同的查询？

Question

为了测试目的，我在一个SQL Server 2008 R2实例上有许多具有相同模式（基本上是相同表和列）的数据库。

我想要一个像这样的查询

SELECT COUNT(*) FROM CUSTOMERS

在实例中的所有数据库上运行。我希望结果有两列：

1- 数据库名称

2- COUNT(*) 的值

示例：

DBName  //   COUNT (*)

TestDB1 // 4

MyDB  // 5

etc...

注意：我假设所有数据库（除了master）中都存在CUSTOMERS表。

- UnDiUdin

---

获取 sp_ineachdb（有关它的文章在这里和这里；下载在这里）。 - Aaron Bertrand

Answer 1

试这个 -

    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

输出（例如，在AdventureWorks中） -

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

- Devart

---

嗨，Devart，我正在使用这个SQL，它非常好用。但是是否有可能添加一个检查，以确定运行此查询的用户是否具有访问每个数据库的权限，如果没有，则查询仍应继续运行并仅返回用户可以访问的数据库。我的目标是确定用户在每个数据库上是否具有DBO角色权限，如果没有，则转到下一个.. 请问你能给出建议吗？ - DK2014

@Charliface，这在最新版本中已经停止工作了。 - Robin Salih

我见过的最好的方法。 - Elvin Ahmadov

Answer 2

简单直接的查询

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

这个查询将展示你想看到的内容，但是对于没有名为“CUSTOMERS”的表的每个数据库都会抛出错误。你需要想出一种逻辑来处理它。

Raj

- Raj

---

+1 这是一个非常紧凑的解决方案，但我选择另一个，因为你的解决方案创建了许多结果表，而另一个则没有。 - UnDiUdin

值得一提的是，sp_MSForEachDB是未记录、不受支持的，并且存在一些已知问题，正如Aaron Bertrand在这里所讨论的 https://sqlblog.org/2020/05/12/bad-habits-to-kick-relying-on-undocumented-behavior - Karl Kieninger

Answer 3

这个怎么样：

这是一个关于IT技术的内容，请看下面：

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

- Dave Sexton

---

这是一个有用的通用代码片段，我只是将 sys.databases 替换为 sys.tables 以实现完全不同的目的，但使用了相同的样板。 - Tomasz Gandor

您在脚本中忘记声明 [at]db_name，需要添加一行：DECLARE @db_name NVARCHAR(150)，否则该脚本非常有用 :-) - inkubpl

临时表在哪里声明？ - Phil3992

@Phil3992 你说得对，应该添加它，但它只是一个包含名称（VARCHAR）和计数（INT）两列的表格。 - Dave Sexton

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

无需游标的解决方案 - 简洁明了

- aquesh

Answer 5

因为我知道这里刚刚提到了一个稍微不同的问题... 如果你只想在某些数据库上执行，那么这些数据库可以存储在某个表中。在这里，我将它们存储在一个临时表中。

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

- CMGeek