?
Solved

SQL Server - I need to retrieve data from columns in a query script. Best way to do this?

Posted on 2015-01-04
10
Medium Priority
?
305 Views
Last Modified: 2015-01-08
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
0
Comment
Question by:bruskhickory
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40530739
>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.  
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.
0
 

Author Comment

by:bruskhickory
ID: 40530776
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
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40530781
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?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:bruskhickory
ID: 40530836
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.
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40530858
So if you can get to the source T-SQL of any given report ... what's the problem?

>I need to identify columns and tables used in a number of SSRS reports.
Define 'a number'.  5, 10, 100, no idea?

There is no automation in SQL Server I know if that does the equivalent of 'get me all of the SSRS data sets that connect to database x' .  The closest I know of is Pragmatic Works DOC xPress, and you still have to locate all of the .rdl files and incorporate them in the project.

Also, just to 'check the box' here, this is an excellent illustration of why it's a good idea to have all of your SSRS reports' data sets as SQL Server Stored Procedures (and NOT T-SQL in the .rdl files), preferably with a prefix like 'rsp_' to imply that it's a SP used in a report.  That way developers don't have to go on a wild goose chase finding T-SQL.   Other reasons for doing this is optimization, better reporting on when SP's ran, and impact analysis.
0
 

Author Comment

by:bruskhickory
ID: 40538941
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.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40538951
Thanks for the accept, but why the 'C'?
0
 

Author Comment

by:bruskhickory
ID: 40538964
Sorry it should have been an A, I can't see anywhere to edit. Not sure what happened there?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40538984
I took the liberty of unaccepting, go ahead and regrade.
0
 

Author Closing Comment

by:bruskhickory
ID: 40539009
Thanks for your help Jim!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question