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
bruskhickoryAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
bruskhickoryAuthor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
bruskhickoryAuthor Commented:
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
 
bruskhickoryAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the accept, but why the 'C'?
0
 
bruskhickoryAuthor Commented:
Sorry it should have been an A, I can't see anywhere to edit. Not sure what happened there?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I took the liberty of unaccepting, go ahead and regrade.
0
 
bruskhickoryAuthor Commented:
Thanks for your help Jim!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.