|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Monday, May 14, 2012 11:01 AM
Points: 101,
Visits: 399
|
|
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
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 8,647,
Visits: 22,398
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either.
SQL Forums are unique among other technical forums for getting exact solutions.. If you provided a CREATE TABLE... and INSERT INTO... scripts, volunteers here can give you a tested, working solution to your questions. Help us help you and provide that and a description of what you are trying to accomplish!
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 14, 2012 11:01 AM
Points: 101,
Visits: 399
|
|
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
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 8,647,
Visits: 22,398
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either.
SQL Forums are unique among other technical forums for getting exact solutions.. If you provided a CREATE TABLE... and INSERT INTO... scripts, volunteers here can give you a tested, working solution to your questions. Help us help you and provide that and a description of what you are trying to accomplish!
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 14, 2012 11:01 AM
Points: 101,
Visits: 399
|
|
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!
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 14, 2012 11:01 AM
Points: 101,
Visits: 399
|
|
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
|
|
|
|
