Solved

SSRS Report of View Create Script Using Query Message

Posted on 2011-03-21
6
352 Views
Last Modified: 2012-05-11
I would like to create an SSRS report that presents the Create View script for a view the user enters as a parameter.  I found this helpful query on this forum to generate the message:

DECLARE @SqlText nvarchar(max)
SET @SqlText = ''
SELECT @SqlText = @SqlText + [Text] FROM sys.syscomments
WHERE id = OBJECT_ID('dbo.Sales')
ORDER BY colid
PRINT @SqlText

I'm struggling with how one accesses the message from a query in SSRS - if that is even possible.  Or is there a better way to do it?

Thanks.
0
Comment
Question by:CDMantel
  • 3
  • 3
6 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 35189920
I understand that you want to create a report that shows the CREATE VIEW statement.  Ideally the user can select the views from a dropdown, right?
To get a list of all views, including their definition and object_id, you can use the query in the snippet.

Note that the query is using sys.sql_modules instead of sys.syscomments (that one is outdated).

But I don't understand what you mean with that last sentence: "I'm struggling with...".  Please clarify if still applicable.
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS view_name, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE o.type = 'V'
ORDER BY o.type ASC;

Open in new window

0
 

Author Comment

by:CDMantel
ID: 35231925
Thanks for responding VelentinoV.

Yes, you are quite right that i'm thinking the user would select from a list of available views in a dropdown.

The query you suggested gathers the needed data.  The "struggle" is in formatting the data (the Create View script in this case) so that it is visually appealing (that is, not breaking at a random point for line wrap).  The SQL Print command will do that.  However, as i understand it, the output of the Print command is as a SQL message.  So my struggle is in accessing this output from SSRS OR using some other method to format the script.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 35239217
Okay, understood what you mean now!  Let's see, perhaps the sp_helptext system procedure can be a solution here?  It returns the object's definition split up into multiple "records".  If you use that procedure to retrieve the view's definition and set up a List in your report to show the records, I think it should work.

See http://msdn.microsoft.com/en-us/library/ms176112.aspx
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:CDMantel
ID: 35255921
True enough.  sp_helptext splits output into multiple records.  Problem is that the location of the splitting seems random.  It doesn't follow the same logic that Print uses.  I can get all the information needed.  it just won't be presented in an easier to read format as Print does.  Is my quest in vain?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 35273442
That's strange.  In my test I'm getting the same results from both methods, with equal line breaks and indentation.  Both methods are returning the view the way it was created, similar to if you would script the view from the Management Studio (right-click view > Script View As > Create To > New Query Editor Window).

If you're looking for even nicer output (including syntax coloring for instance) then I think you'll need to look at developing a Custom Report Item.  But the effort needed would be of course much higher than with a query and a List control.
If you're up to it,  here's a link to get you started: http://msdn.microsoft.com/en-us/library/ms345231.aspx
0
 

Author Comment

by:CDMantel
ID: 35295204
Wow.  My bad.  You're right.  Sending the query output to text proves the point.  Thanks very much for your help (including the link for custom report item).
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
Encrypting SQL Server 2014 or SQL Server 2016 4 29
SQL SERVER - Index skipped a number 2 25
SQL Query 2 31
Query for timesheet application 3 16
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

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