30

I am new to SQL Server, and I am sorry if there is an obvious solution to my question but I can't seem to find it.

I am looking to generate a report (or list) of all the data sources and their individual dependencies on an SQL Server 2008 R2 (reporting server).

I know that I can access each individual data source to get a list of all the items that are dependent on it. I have done this in the past but it is time consuming.

Is there a way to get a report that would display all the data sources and their dependent items?

Thanks in advance,

Marwan

Marwan مروان
  • 2,163
  • 8
  • 30
  • 40
  • What would you categorize as a dependent item? Only models, datasets, and reports, or are there others? – Bryan Mar 09 '12 at 19:14
  • 2
    @beargle - I would define a dependent item as any report that uses the data source. Currently, each data source has a "View Dependent Items" in its menu. So anything that would be listed in that would qualify as an dependent item. I hope that helps clarify my question/request. Thanks. – Marwan مروان Mar 09 '12 at 19:25

3 Answers3

40

The following (which was modified from what beargle posted earlier) does what I was looking for. This will list all the data sources by their actual name, and all their dependent items:

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 مروان
  • 2,163
  • 8
  • 30
  • 40
9

This query should be run against the ReportServer database

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;

The dependent items page in Report Manager executes the dbo.FindItemsByDataSource stored procedure, supplying these parameters: ItemID = <data source item ID> and AuthType = 1. The above query is a hacked version of the query used by this stored procedure to remove the data source specific ID. This allows dependent items to be returned for all data sources. I removed the data sources themselves from the results with DS.Name IS NOT NULL

Bryan
  • 17,112
  • 7
  • 57
  • 80
  • This looks great. I hope I am not sounding ignorant here, but how do I run this. My experience so far has been to build and run SSRS on the server. I am not sure how to run your query. Thanks in advance. – Marwan مروان Mar 15 '12 at 15:50
  • Treat the query above as if it was part of a normal report project; Define the data source, use this query to build the data set, then add the fields to your report. Alternatively, run it directly against the `ReportServer` database using SQL Server Management Studio, Visual Studio, or some other tool. – Bryan Mar 15 '12 at 16:13
  • Thank you so much **beargle**, your query worked perfectly. Thank you also for taking the time to explain the details on it and how to run it. Extremely helpful. Thanks again. – Marwan مروان Mar 16 '12 at 17:18
  • How can substitute the data source names themselves (filtered out as NULL in your query above) for the data source names that are being returned. In the ReportServer that I am working with, the two are not the same. I hope that I phrased my question properly. Thanks. – Marwan مروان Mar 16 '12 at 22:53
  • I answered my own question below. – Marwan مروان Mar 20 '12 at 15:25
5

You might also consider using 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
  • 51
  • 1
  • 2