I created the following script in response to a question on the microsoft.public.sqlserver.olap news group. Someone had posted a question about how to list all the source tables for an Analysis Services database that they had inherited. Using a one of my other scripts as a shell it was not too hard to pull this together, hopefully other will find it useful also.

To run the script issue the following command from a command shell. You will need to be logged on as a user with OLAP Administrator priviledges to get this script to run.

cscript ListSourceTables.vbs []

To save the output to a file simply use command line redirection

eg. cscript ListSourceTables.vbs MyServer > SourceTables.csv

would list all of the databases from the “MyServer“ Analysis Server to a file called SourceTables.csv

Copy the following script to a file called ListSourceTables.vbs

'\\ ============= START SCRIPT ===============

'----------------------------------------------------------------------------------------------
' Name       : ListSourceTables.vbs
' Author     : Darren Gosbell (DPG)
' Date       : 1 Oct 2005
' Description: Lists all of the Source tables for cubes and dimensions.
' Notes      : From a Command prompt type the following
'
'              cscript ListSourceTables.vbs []
'
'              (if you don't specify a database name all databases will be listed)
'
' Revision History:
' Date          Who Ref#    Description
' 01/10/2005    DPG n/a     Initial version
'----------------------------------------------------------------------------------------------

Dim dsoServer'As DSO.Server
Dim dsoCube 'As DSO.Cube
Dim mdCube  'As DSO.MDStore
Dim dsoDim  'As DSO.Dimension
Dim dsoDb   'As DSO.Database
Dim vbCrLf  'As String

vbCrLf = chr(13) & chr(10)

'\\ Get arguments from command line
If wscript.arguments.count = 2 then
 sServer = wscript.arguments(0)
    sDatabase = wscript.arguments(1)
ElseIf wscript.arguments.count = 1 then
 sServer = wscript.arguments(0)
 sDatabase = ""
Else
    sMsg = wscript.scriptname & " expects 1 or 2 arguements to be supplied " & vbCrLf _
           & "       - the Name/IP Address of the Analysis Services Server" & vbCrLf _
           & "     - the Name of the Database" & vbCrLf _
           & vbCrLf _
           & "This script will then list all of the source tables for " & vbCrLf _
           & "the cubes and dimensions in the database." & vbCrLf
    wscript.echo sMsg
    wscript.quit(1)
End If

'\\ Connect to the server
Set dsoServer = CreateObject("DSO.Server")
dsoServer.Connect sServer
If Err.Number <> 0 then
    wscript.echo "ERROR: Could not connect to the Server: " & sServer & vbCrLf & err.Description
    wscript.quit(1)
End If   

PrintHeader

if sDatabase = "" then

    For Each dsoDb In dsoServer.MDStores
        ProcessDatabase dsoDb
    Next 'dsoDb
Else
 Set dsoDB = dsoServer.MDStores(sDatabase)
 if dsoDb IS Nothing then
  wscript.echo "Error accessing Database: " & sDatabase & vbCrLf
  wscript.echo "Please check that the name is correct"
  wscript.quit(2)
 else
  ProcessDatabase dsoDb
 End If
End If

dsoServer.CloseServer
   
   
   
'===========================
'\\ Processes each object in the database
Sub ProcessDatabase(dsoDb)
    clsCube = 9
 sbclsRegular = 0

    For Each dsoCube In dsoDb.MDStores
        Set mdCube = dsoCube
       
        If mdCube.ClassType = clsCube And mdCube.SubClassType = sbclsRegular Then
       
            PrintCubeSource dsoCube
           
            For Each dsoDim In mdCube.Dimensions
         If dsoDim.SubClassType = sbclsRegular Then
                 PrintDimSource dsoDim
                End If
            Next 'dsoDim
        End If
    Next 'dsoCube
End Sub

'===========================
'\\ The following routines control what columns are printed out
Sub PrintHeader()
    wscript.echo "OBJECT_TYPE,OBJECT_NAME,SOURCE_TABLE,SOURCE_TABLE_FILTER"
End Sub

'===========================
Sub PrintCubeSource(dsoCube) 'As DSO.Cube)
    wscript.echo """CUBE"",""" & dsoCube.Name & """," & dsoCube.SourceTable & "," & dsoCube.SourceTableFilter
End Sub

'===========================
Sub PrintDimSource(dsoDim) ' As DSO.Dimension)
    wscript.echo """DIM,""" & dsoDim.Name & """," & dsoDim.SourceTable & "," & dsoDim.SourceTableFilter
End Sub