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
268 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
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now