January 4, 2012 at 12:59 pm
I am building a mini-app that will give some power users the ability to browse tables and views in selected reporting databases, and create and execute queries, without having SSMS. In essence it is a stripped down query analyzer/object browser. I would like to be able to show them view definitions in the same way ssms does it when you right click a view and script it to a new window as ALTER VIEW ...... I don't want the entire 'ALTER VIEW' etc., just the SELECT statement. This will go a long way towards helping them build their own queries, because they'll be able to take one that is close and alter it.
Somehow the tabs, returns, etc. formatting in the original CREATE VIEW statement (as shown in sys.sql_modules) is rendered faithfully by SSMS -- and I can't think of anywhere it would reside other than in the definition column. One reason I suspect that is because when I create two views, one perhaps with a line feed or two before the CREATE VIEW ... and the other without any returns, they show up in SSMS when I script them out.
However they are nowhere to be seen in the definition column of sys.sql_modules. If I take that text and paste it into an open query window in SSMS, they come through as one line.
Does anyone know how to get the formatting along with the text? thx. d lewis
January 4, 2012 at 1:04 pm
David, i think it has to do with CHAR(10) vs CHAR(13) + CHAR(10) as the line terminator.
SSMS will show you the definition the "right" way, regarless of the terminator, but if you are putting the definition in, say , a textbox, it might look wrong if it only has CHAR(10).
this is what I do for a very similar situation..it's just a double REPLACE to get the definition in the expected vbCrLf format i want:
SELECT
objz.object_id AS [ViewID],
objz.name AS [ViewName],
objz.schema_id AS [SchemaID],
SCHEMA_NAME(objz.schema_id) AS [SchemaName],
REPLACE(REPLACE(MODS.[definition], CHAR(10), CHAR(13) + CHAR(10)), CHAR(13) + CHAR(13) + CHAR(10), CHAR(13) + CHAR(10)) AS definition
FROM sys.sql_modules MODS
INNER JOIN sys.objects objz
ON objz.object_id = MODS.object_id
WHERE objz.type_desc IN( 'VIEW' )
AND objz.is_ms_shipped = 0
Lowell
January 4, 2012 at 2:15 pm
Lowell: I will experiment a little with that. However, when I run your query as you've written it, the copy a definition into SSMS it comes through as one line; when I copy it into Textpad it also comes through as one line...
David
January 4, 2012 at 2:22 pm
DavidL (1/4/2012)
Lowell: I will experiment a little with that. However, when I run your query as you've written it, the copy a definition into SSMS it comes through as one line; when I copy it into Textpad it also comes through as one line...David
yea, paste it into something RTf sensitive; Outlook, Word, wordpad, some email client...it'll render pretty for an additional copy/paste to get it the way you see it;
the copy paste(correctly!) from browser to texteditor can depedn on what you use...Chrome, IE, Firefox.
Lowell
January 4, 2012 at 2:31 pm
I think I see what I was not doing right -- I had 'results to grid', and when I switch it to 'results to text' and copy and paste that result to notepad, I get the expected line feed behavior. Now, I'll tackle the tab/indentation part. Thanks!
January 4, 2012 at 3:29 pm
I think I have found an alternative solution that with some tweaking will work. sp_helptext 'view name here', in conjunction with a temp table, will enable me to strip out the CREATE VIEW etc. part and present only the query definition, with formatting.
For example:
create table #text(ID INT IDENTITY(1,1) NOT NULL,line varchar(8000))
insert into #text
exec sp_helptext 'dbo.vwInvRpt'
select [Line] from #text WHERE ID>
(SELECT ID FROM #text WHERE [Line] LIKE 'CREATE VIE%')
AND [Line] NOT LIKE '--%'
AND [Line] NOT LIKE 'AS%'
drop table #text
Returns the following:
/*
08-075-2010 RRM Created for use in Inventory Snapshot Report
*/
SELECT I.ProductID,P.Description,I.LotNum,I.Qty,I.Class,I.HoldCode
FROM dbo.SN_INVRPT AS I
inner join dbo.Products AS P ON P.ProductID = I.ProductID
Thanks! David
July 12, 2017 at 2:07 pm
Lowell - Wednesday, January 4, 2012 1:04 PMDavid, i think it has to do with CHAR(10) vs CHAR(13) + CHAR(10) as the line terminator.SSMS will show you the definition the "right" way, regarless of the terminator, but if you are putting the definition in, say , a textbox, it might look wrong if it only has CHAR(10).this is what I do for a very similar situation..it's just a double REPLACE to get the definition in the expected vbCrLf format i want:SELECT objz.object_id AS [ViewID], objz.name AS [ViewName], objz.schema_id AS [SchemaID], SCHEMA_NAME(objz.schema_id) AS [SchemaName], REPLACE(REPLACE(MODS.[definition], CHAR(10), CHAR(13) + CHAR(10)), CHAR(13) + CHAR(13) + CHAR(10), CHAR(13) + CHAR(10)) AS definitionFROM sys.sql_modules MODSINNER JOIN sys.objects objz ON objz.object_id = MODS.object_idWHERE objz.type_desc IN( 'VIEW' ) AND objz.is_ms_shipped = 0
Thank you so much! This was exactly what I needed.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply