选择仅包含特定表的数据库

2022-01-16 00:00:00 sql sql-server sql-server-2008-r2

我正在寻找一种方法来选择我的 sql 服务器上的所有数据库，其中只包含表dbo.mytable"

I'm looking for a way to select all databases on my sql server, which only contain the table "dbo.mytable"

我该怎么做?

我已经有了这两个 sql 查询:

I already have these two sql queries : 

Select name From sys.databases Where database_id > 5

和

IF EXISTS
    (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[mytable]') AND type in (N'U')) 
  Select 1 [Exists]
Else
  Select 0 [Exists]

第一个查询列出了我的 sql server 上的所有数据库，第二个查询是否存在 dbo.mytable.我想合并它们.

The first query, lists all databases on my sql server, and the second checks if dbo.mytable exists. I would like to merge them.

谢谢

推荐答案

一种将它们全部返回到一个结果集中的简洁方法是

A concise way that brings them all back in one resultset is

SELECT name
FROM   sys.databases
WHERE  CASE
         WHEN state_desc = 'ONLINE' 
              THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[mytable]', 'U')
       END IS NOT NULL

相关文章