主页 / dba / 问题 / 199961
Accepted
Francesco Mantovani
Asked: 2018-03-12 23:49:24 +0800 CST

在所有数据库中查找特定的表和列值

  • 772

这是一个关于逻辑的问题，可以帮助我更多地了解如何迭代到我的数据库中。

在我的服务器上，我有几个 AdventureWorks* 数据库：

在此处输入图像描述

我有一个冷查询帮助我遍历我的所有数据库 AdventureWorks* 而不是：

CREATE TABLE #LIst_DB (name nvarchar(128))

INSERT INTO #LIst_DB 

select name from  sys.databases
WHERE database_id > 4
AND state = 0;

select * from #LIst_DB

DROP TABLE #LIst_DB

对于每个 AdventureWorks* 数据库，我想AWBuildVersion在每个 DB 旁边列出：

在此处输入图像描述

此时我很困惑，因为有两个问题我不知道如何解决：

  1. 如何select [Database Version] from AWBuildVersion为每个数据库运行一个？
  2. 如果数据库没有AWBuildVersion表怎么办？

在这里我找到了这个查询：

DECLARE @SQL NVARCHAR(max)

SET @SQL = stuff((
            SELECT '
UNION
SELECT ' + quotename(NAME, '''') + ' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name
FROM ' + quotename(NAME) + '.sys.tables WHERE NAME =  @TableName '
            FROM sys.databases
            ORDER BY NAME
            FOR XML PATH('')
                ,type
            ).value('.', 'nvarchar(max)'), 1, 8, '')

--PRINT @SQL;

EXECUTE sp_executeSQL @SQL
    ,N'@TableName varchar(30)'
    ,@TableName = 'AWBuildVersion'

这使我接近我的目标，但它显示了AWBuildVersion我需要列 Database Version = 11.0.2100.60的表名

在此处输入图像描述

sql-server system-tables

3 个回答

  1. 我不想用 T-SQL 这样做，因为它限制了我可以从中获得结果的服务器数量。

    相反，我会创建一个这样的 PowerShell 脚本：

    $servers = @( 'server1', 'server2')

$query = "SELECT [Database Version] AS DatabaseVersion FROM [dbo].[AWBuildVersion];"

$results = @()
$comment = @()

# Loop through the servers
foreach ($server in $servers) {
    $databases = Get-DbaDatabase -SqlInstance $server | Where-Object {$_.Name -like 'AdventureWorks*'}
    Write-Host "Going through server $server"

    # Loop through the databases
    foreach ($database in $databases) {
        # Reset variables
        $result = @()
        $data = $null
        $comment = $null

        if ($database.Tables.Name -contains 'AWBuildVersion') {
            Write-Host "- Querying database $database"

            $data = Invoke-DbaSqlQuery -SqlInstance $server -Database $database.Name -Query $query 
        }
        else {
            $comment += 'Could not find table AWBuildVersion'
        }

        $result = [PSCustomObject]@{
            Server          = $server
            Database        = $database.Name
            DatabaseVersion = $data.DatabaseVersion
            Comment         = $comment -join ', '
        }

        $results += $result
    }
}

$results

    脚本的结果

    您确实需要dbatools powershell 模块来运行它。

    它的作用是：

    • 遍历您分配的服务器。
    • 检索名称中包含“AdventureWorks”的数据库。
    • 遍历数据库并执行查询。
    • 如果它找不到表格，它会在找不到表格的结果中添加注释。
    • 4
  2. Best Answer

    内联评论：

    -- The table to find
DECLARE @find nvarchar(257) = N'dbo.AWBuildVersion';

-- Holds results
DECLARE @results table 
(
    [Db_Name] sysname PRIMARY KEY,
    [Database Version] nvarchar(25) NOT NULL
);

-- Current database   
DECLARE @db sysname;

-- AdventureWorks databases cursor
DECLARE dbs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT D.[name] 
FROM sys.databases AS D
WHERE 
    D.[name] LIKE N'AdventureWorks%'
    AND D.state_desc = N'ONLINE'
    AND DATABASEPROPERTYEX(D.[name], 'Collation') IS NOT NULL
ORDER BY
    D.[name];

OPEN dbs;

WHILE 1 = 1
BEGIN
    -- Next database
    FETCH dbs INTO @db;
    IF @@FETCH_STATUS = -1 BREAK;

    -- Find [Database Version] if @find table exists
    INSERT @results ([Db_Name], [Database Version])
    EXECUTE sys.sp_executesql
        N'
            -- Construct 3-part object name
            DECLARE @object nvarchar(386) = 
                QUOTENAME(@db) + N''.'' +
                ISNULL(QUOTENAME(PARSENAME(@find, 2)), '''') + N''.'' +
                QUOTENAME(PARSENAME(@find, 1));

            -- Query to find [Database Version]
            DECLARE @sql nvarchar(max) = N''
                SELECT 
                    Db_Name = '''''' + @db + '''''',
                    [Database Version] 
                FROM '' + @object;

            IF OBJECT_ID(@object, N''U'') IS NOT NULL
                EXECUTE (@sql)',
        N'@db sysname, @find nvarchar(257)',
        @db = @db, @find = @find;
END;

CLOSE dbs; DEALLOCATE dbs;

SELECT
    R.[Db_Name],
    [Table_Name] = @find,
    R.[Database Version]
FROM @results AS R
ORDER BY
    R.[Db_Name];
    • 4

  3. 我可能会使用sp_foreachDBBrent Ozar 的First Responder Kit中的内容：

    EXEC dbo.sp_foreachDB @command1 = 'select [Database Version] from dbo.AWBuildVersion', 
                 @name_pattern = 'AdventureWorks%'

    尚未对此进行测试，但我相信语法应该可以工作。

    • 2

相关问题