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
281 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
  • 5
  • 5
10 Comments
 
LVL 65

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 65

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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 65

Accepted Solution

by:
Jim Horn earned 500 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 65

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 65

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

808 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