SQL Server - I need to retrieve data from columns in a query script. Best way to do this?
Posted on 2015-01-04
I need to identify columns and tables used in a number of SSRS reports. I want to create an SQL script which I can run to retrieve columns by either pasting in specific sql query or extracting directly from an SSRS report .rdl file to then generate a temp table or even a flat file containing the table identifier and the columns used in the sql query of that report.
The scripts have many joins so they will have different table identifiers.
The problem is getting the column names from the sql scripts used in the SSRS reports.
I was thinking I could manually declare the table identifiers at the start e.g. Risk, Controls, .. and that would be used to identify the columns from the query. E.g. Risk.Description, Risk.Name, or Control.Desciption, Control.Name, etc. Do a search on each row of data for [table] + ‘.’ And then retrieve into an array or temp table the value after that dot and only store the distinct items found for each table identifier and column. E.g. a number of columns could be named the same but have different table identifiers. I would want e.g. ‘Name’ recorded for both Risk and Controls.
I have many reports I need to do this to but I would only need to do this once to identify columns used in each of the reports.
I am not a programmer but did it at university many years ago so I can’t remember much now but it wouldn’t take me much to figure it out. I have good knowledge of sql but only really used it for joining tables.
Can you tell me how I would do this?
Example end result output