List of used tables and fields

[rpitts22]

Hi, I have been trying to use the command to Extract the tables and field from the reports of my report directory. However, the output-file is simply blank. The other commands work, but do not give me what I am after. Is there something I am missing with this? I would like to see what tables and fields are used in each report.

Thanks,
Ryan

[craibuc]

Please post a code sample.

[rpitts22]

When I run this, a blank csv file is created. I ran the code to get the unique list of tables used, but it does not give me anything for each individual report. I would like to be able to get tables and fields used for each individual report is possible:

Get-ChildItem '\ServerName\reports' *.rpt -Recurse | % {
$report = $_

# TODO

$_.Subreports | % {
    $subreport = $_

# TODO

} # /Subreports

} | ConvertTo-Csv -NoTypeInformation | Out-File C:\Users\MyName\Desktop\reports.fields.csv

[craibuc]

I didn't implement any of the code, hence the #TODO tags.

This will get you the tables, but you'll need to examine each table for the columns.

    $_.Database.Tables | Select-Object location -Expand location
    $_.Subreports | % { $_.Database.Tables | Select-Object location -Expand location }

[rpitts22]

Thanks for the quick response on this. I apologize if I am not understanding this correctly. When I try your statements, I am still getting a blank output. Am I missing something here?

Get-ChildItem '\\ServerName\reports' *.rpt -Recurse | % {
$report = $_
$_.Database.Tables | Select-Object location -Expand location
$_.Subreports | % { $_.Database.Tables | Select-Object location -Expand location}
$subreport = $_
} | ConvertTo-Csv -NoTypeInformation | Out-File C:\Users\MyName\Downloads\reportfields.csv

[craibuc]

You should try the Get-DataDefinition function.

[rpitts22]

Thanks, I was able to get the tables and fields used in every report along with any subreports.

[craibuc]

Maybe you could modify the example to include your code, then send a pull request.

[rpitts22]

I do not know how to create a pull request, but I can include what worked for me.

Get-ChildItem '\report\directory' *.rpt -Recurse | Open-Report -Verbose | % {

$report = $_
    $_.Database.Tables.Fields | Where-Object {$_.UseCount -gt 0} | Foreach { [PsCustomObject]@{
    		FileName=(Split-Path $report.FilePath -Leaf)
    		Table=$_.TableName
    		Field=$_.Name}}

$_.Subreports | % {
	$subreport = $_
	$_.Database.Tables.Fields | Where-Object {$_.UseCount -gt 0} | Foreach {[PsCustomObject]@{
		FileName=(Split-Path $report.FilePath -Leaf)
		Subreport=$subreport.Name
		SubTable=$_.TableName
		SubField=$_.Name}}}	

} | Select-Object FileName, Table, Field, Subreport, SubTable, SubField | Sort-Object FileName, Table, Field, Subreport, SubTable, SubField | Export-csv ~\downloads\ReportTablesAndFields.csv

[craibuc]

Thanks. I modified the example.

[rpitts22]

Going on a limb here... Do you think there is a way to use your module to gather the tables used within a SQL Command Alias? I would like to quickly pull the tables and associated fields used within reports that use SQL rather than regular joins. Most commands rename tables to letters, like Orders AS o, this might make things more complex.

[craibuc]

You can use it to get the command's text easily. Parsing that is the challenge. I had some success using SQL Parser for this task. As I recall, their object model was hard to traverse.