时间戳

首页 > 解决方案 > 如何加入 sys.databases、sys.tables 和 sys.columns

sql-server - 如何加入 sys.databases、sys.tables 和 sys.columns

问题描述

我必须检查 sql server 实例的数据库子集中的表子集中的值是否存在。请注意我需要这样做，因为我有 30 个具有相同模式名称和相似结构的数据库。单独查询所有数据库是浪费时间。

该查询为现有表生成正确的代码，但对表中是否存在列的附加检查失败。某些表中的列不存在，因此生成的代码不得包含对没有此列的表的查询。

为了解决这个问题，我需要真正找到一种方法将 sys.databases 与 sys.tables 和 sys.columns 连接起来。或者以节省时间的方式查询所有所需数据库的替代方法。

SET NOCOUNT ON;

IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
   DROP TABLE #temp

CREATE TABLE #temp
(
      exist INT
    , DB VARCHAR(50)
    , tbname VARCHAR(500)
)

/*tables common root, 
all tables i need to query start with this prefix and a number between 1 and 50
and some resulting tables do not exist
ex: dbo.Z_WBL_ASCHEDA23 exist in wbcto, while dbo.Z_WBL_ASCHEDA23 does not exist in db wbgtg    
*/

DECLARE @TableName NVARCHAR(200) 
SELECT @TableName = 'dbo.Z_WBL_ASCHEDA'

DECLARE @SQL NVARCHAR(MAX)

;WITH n(n) AS
(
    SELECT 1
    UNION ALL
    SELECT n+1 FROM n WHERE n < 50
)
SELECT @SQL = STUFF((
    SELECT CHAR(13)+'SELECT COUNT(1), ''' + db.name + ''', '''+
        @TableName+CONVERT(VARCHAR, n.n)+'''  FROM ' +@TableName+CONVERT(VARCHAR, n.n) 
        + ' WHERE COALESCE(s_dettagli,'''') = ''CONTROLLATO'' '
        +CHAR(13)
    FROM sys.databases db
    INNER JOIN n ON 1=1
    INNER JOIN sys.tables t ON OBJECT_ID(db.name + '.' + @TableName+CONVERT(VARCHAR, n.n)) IS NOT NULL
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID and c.name = 's_dettagli' 
    /*join on columns not working, generates sql for tables without 's_dettagli' column and query fails*/
    WHERE db.name like 'wb%' --check only databases starting with 'wb'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

select @SQL

INSERT INTO #temp (exist, DB, tbname)              
EXEC sys.sp_executesql @SQL

SELECT * 
FROM #temp t
where exist <> 0

编辑：添加一些从查询生成的 sql

SELECT COUNT(1), 'wb360', 'dbo.Z_WBL_ASCHEDA23'  FROM wb360.dbo.Z_WBL_ASCHEDA23 WHERE COALESCE(s_dettagli,'') = 'CONTROLLATO'  
SELECT COUNT(1), 'Wbbim', 'dbo.Z_WBL_ASCHEDA32'  FROM Wbbim.dbo.Z_WBL_ASCHEDA32 WHERE COALESCE(s_dettagli,'') = 'CONTROLLATO'

第一个查询的表不包含“s_dettagli”列

EDIT2：解决方案

EXEC sp_MSforeachdb '
IF ''?'' not like ''wb%'' 
    RETURN
USE [?]
EXEC sp_MSforeachtable 
@replacechar = ''!'', 
@command1 = ''SELECT ''''?'''' AS db_name, ''''!'''' AS table_name, COUNT(*) FROM !  '',
@whereand = '' And Object_id In (
                                Select t.Object_id 
                                From sys.objects t
                                INNER JOIN sys.columns c on c.Object_id = t.Object_id
                                Where t.name like ''''Z_WBL_ASCHEDA%''''
                                AND c.name = ''''s_dettagli'''' )'' '

标签： sql-server

解决方案

Sys.columns 可以使用 object_id 字段连接到 sys.tables（object_id 是表本身的表示）。

sys.tables 在您正在查询的数据库的上下文中运行，因此您看不到另一个数据库中包含的表。sys.databases 可以在实例上的任何数据库上运行，并允许您查看同一实例上的其他数据库。因此，您不需要将表加入数据库（这也是 sys.tables 中没有 database_id 字段的原因）。

我希望这会有所帮助。任何澄清请让我知道。

推荐阅读