SQL Server - I need to retrieve data from columns in a query script. Best way to do this?
Hi Experts,
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
Identifier Columns
Risk Name
Risk Desc
Control Name
Control Etc
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
Identifier Columns
Risk Name
Risk Desc
Control Name
Control Etc
ASKER
Hi Jim, sorry I am also dyslexic and find it difficult to explain things.
I just wanted to create some automatic way to retrieve columns from a report file or copying the sql query from the dataset and using that. Using for example a table name plus a '.' as unique way to identify the columns. I will download the UltraEdit software now to see if that will do the trick. I will let you know.
Cheers
I just wanted to create some automatic way to retrieve columns from a report file or copying the sql query from the dataset and using that. Using for example a table name plus a '.' as unique way to identify the columns. I will download the UltraEdit software now to see if that will do the trick. I will let you know.
Cheers
Do you have the ability to open up SSRS, open up one of these .rdl files, then in the left treeview find Data Sets, open that, and view the contents?
ASKER
Yes I do. I can do that fine. I have many datasets which also have many joins to other tables with hundreds of columns so locating the columns names is a long manual process. This is only a small part of the process, I need to do a lot more to it after that. So instead of doing this manually I wanted to get an instant fix I guess to save time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Jim,
Thanks for your comments and information. I've completed the work and used your Ultra Edit software you send in the link to do it. Thanks.
Thanks for your comments and information. I've completed the work and used your Ultra Edit software you send in the link to do it. Thanks.
Thanks for the accept, but why the 'C'?
ASKER
Sorry it should have been an A, I can't see anywhere to edit. Not sure what happened there?
I took the liberty of unaccepting, go ahead and regrade.
ASKER
Thanks for your help Jim!
I've read this line three times, and the text after it, and it still doesn't make sense.
All SSRS reports have data sets, which are either T-SQL or a Stored Procedure/Function/call.
So either open the SSRS .rdl up and look at the T-SQL, or identify the SP name and then look at that.
Or if you're talking a large number of .rdl files and want to validate if certain tables are used, you can open any file editing tool such as Ultra Edit and do a compare across all files, as .rdl's are also searchable .xml files.
>I am not a programmer but did it at university many years ago so
In that case I recommend explaining your question at Barney-level simple English, and not try to throw in as many technical terms as possible.