在SQL Server 2008 R2中列出所有数据源及其依赖关系（报表、项目等）

Question

我是SQL Server的新手，如果我的问题有一个明显的解决方案，但我似乎找不到它，对此我感到抱歉。

我想生成一个报告（或列表），列出所有数据源及其在SQL Server 2008 R2（报告服务器）上的相应依赖关系。

我知道可以访问每个单独的数据源以获取其所有依赖项的列表。我以前做过这件事，但很费时间。

是否有一种方法可以获得显示所有数据源及其依赖项的报告？

提前致谢，

Marwan

- Marwan مروان

---

您将哪些物品归类为依赖项？仅限于模型、数据集和报告，还是还有其他的物品？ - Bryan

@beargle - 我认为依赖项是指使用数据源的任何报告。目前，每个数据源都在其菜单中有一个“查看依赖项”的选项。因此，任何列在其中的内容都可以被视为依赖项。希望这能够澄清我的问题/请求。谢谢。 - Marwan مروان

Answer 1

以下代码是在beargle的代码基础上修改而来，实现了我需要的功能。它将列出所有数据源及其依赖项：

SELECT
    C2.Name AS Data_Source_Name,
    C.Name AS Dependent_Item_Name,
    C.Path AS Dependent_Item_Path
FROM
    ReportServer.dbo.DataSource AS DS
        INNER JOIN
    ReportServer.dbo.Catalog AS C
        ON
            DS.ItemID = C.ItemID
                AND
            DS.Link IN (SELECT ItemID FROM ReportServer.dbo.Catalog
                        WHERE Type = 5) --Type 5 identifies data sources
        FULL OUTER JOIN
    ReportServer.dbo.Catalog C2
        ON
            DS.Link = C2.ItemID
WHERE
    C2.Type = 5
ORDER BY
    C2.Name ASC,
    C.Name ASC;

- Marwan مروان

---

@beargle - 感谢您帮助我解决了这个问题 (^_^) - Marwan مروان

第一次尝试就成功了。非常好的答案。我所要做的就是更改数据库名称。 - Fuji - H2O

Answer 2

此查询应针对ReportServer数据库运行。

SELECT
    DS.Name AS DatasourceName,
    C.Name AS DependentItemName, 
    C.Path AS DependentItemPath
FROM
    ReportServer.dbo.Catalog AS C 
        INNER JOIN
    ReportServer.dbo.Users AS CU
        ON C.CreatedByID = CU.UserID
        INNER JOIN
    ReportServer.dbo.Users AS MU
        ON C.ModifiedByID = MU.UserID
        LEFT OUTER JOIN
    ReportServer.dbo.SecData AS SD
        ON C.PolicyID = SD.PolicyID AND SD.AuthType = 1
        INNER JOIN
    ReportServer.dbo.DataSource AS DS
        ON C.ItemID = DS.ItemID
WHERE
    DS.Name IS NOT NULL
ORDER BY
    DS.Name;

报告管理器中的相关项页面执行 dbo.FindItemsByDataSource 存储过程，提供以下参数：ItemID = <数据源项 ID> 和 AuthType = 1。上述查询是该存储过程使用的查询的一个破解版本，以删除数据源特定的 ID。这允许返回所有数据源的相关项。我使用 DS.Name IS NOT NULL 从结果中删除了数据源本身。

- Bryan

---

这看起来很不错。我希望我说话不会显得无知，但是我该如何运行它呢？到目前为止，我的经验是在服务器上构建和运行SSRS。我不确定如何运行你的查询。提前感谢。 - Marwan مروان

将上述查询视为普通报告项目的一部分；定义数据源，使用此查询构建数据集，然后将字段添加到报告中。或者，可以直接在SQL Server Management Studio、Visual Studio或其他工具中针对“ReportServer”数据库运行它。 - Bryan

非常感谢 beargle，你的查询完美地运行了。也感谢你抽出时间解释它的细节和如何运行它。非常有帮助。再次感谢。 - Marwan مروان

如何将数据源名称本身（在上面的查询中被过滤为空）替换为正在返回的数据源名称。在我使用的ReportServer中，两者并不相同。我希望我的问题表述得当。谢谢。 - Marwan مروان

下面是我自己的答案。 - Marwan مروان

Answer 3

您可能还考虑使用PowerShell：

    #************************************************************************************************************************************
# FileName:     Delete-DataSources.ps1
# Date:         2015/04/23
# Author:       Hugh Scott
#
# Description:
# This script finds data sources with no dependencies in SSRS and removes them.
#
# Parameters:
#   $serverBase     - base URL for the server to check (ie, myserver.mydomain.com)
#   [$WhatIf]       - Option wwitch parameter to prevent actual deleting of objects (will list out reports that need to be deleted)
#***********************************************************************************************************************************
[CmdletBinding()]
Param(
    [Parameter(Mandatory=$true,Position=0)]
    [string]$serverBase,
    [Parameter(Mandatory=$false,Position=1)]
    [switch]$WhatIf
)

$url = "http://$serverBase/reportserver/ReportService2010.asmx?WSDL"
$ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential -Namespace "ReportingWebService"

$outFile = ".\DeleteItems_$serverBase.txt"

# Connection to Web Service, grab all data sources
$items = $ssrs.ListChildren("/", $true) | where-object {$_.typename -eq "DataSource"}
foreach($item in $items) {

    $dependencies = $ssrs.ListDependentItems($item.Path)
    $dependentReports = $dependencies.Count

    if($dependencies.Count -eq 0){
        [string]$itemName = $item.Path
        if($WhatIf){

            Write-Host "Item $itemName would be deleted."
            Add-Content $outFile "Item $itemName would be deleted."
        } else {
            try {
                $ssrs.DeleteItem($item.Path)
                Write-Host "Item $itemName deleted."
                Add-Content $outFile "Deleted item $itemName ."
            } catch [System.Exception] {
                $Msg = $_.Exception.Message
                Write-Host $itemName $Msg
                Add-Content $itemName $msg
            }
        }
    }
}

- hmscott