Hi experts, I need to script several views on a regular basis. Is there a way to run multiple sp_helptext statements and have a SINGLE block of code output? The go generates a separate output window.
Hope this makes sense. Thanks
sp_helptext vw_View1;
go
sp_helptext vw_View2;
go
You could load all the defs into a temp table and then do one final SELECT from that temp table:
CREATE TABLE #text ( id int IDENTITY(1, 1) NOT NULL, text_line varchar(max) NULL );
INSERT INTO #text VALUES('/**** vw_view1 definition /')
INSERT INTO #text EXEC sys.sp_helptext vw_View1;
INSERT INTO #text VALUES('/ vw_view2 definition ****/')
INSERT INTO #text EXEC sys.sp_helptext vw_View2;
SELECT text_line FROM #text ORDER BY id
This is exactly what I needed, per usual 
Thank you!
Just another way to do it but with all the views in one spot. No need for an IDENTITY column when OBJECT_ID or ViewName makes a great unique natural key.
DROP TABLE IF EXISTS #ViewCode;
WITH cteObjId AS
(
SELECT ObjectID = object_id(v.ViewName)
,v.ViewName
FROM (VALUES --Keep your simple list of views here.
('dbo.iFunction')
,('dbo.GetRandom')
,('dbo.StagingFmt')
,('dbo.vFileContent')
)v(ViewName)
)
SELECT cte.ViewName
,cte.ObjectID
,ViewCode = CONCAT(def.definition,NCHAR(10),'GO',NCHAR(10))
INTO #ViewCode
FROM cteObjId cte
JOIN sys.objects obj
ON obj.object_id = cte.ObjectID
AND obj.type = 'V' --Make sure that what we're finding is a VIEW
JOIN sys.sql_modules def
ON def.object_id = cte.ObjectID
;
SELECT * FROM #ViewCode ORDER BY ViewName
;How is view name alone a "unique key" when each view has multiple rows as part of its definition??
And even if viewname would be unique, it could be 30, 40, 50 chars. Why repeat that on every row when a single 4-byte integer will work and will sort much more efficiently?
No sir. The only time that you see multiple rows is during display that supports multiple rows and SSMS returns multiple rows in the damned places to keep humans happy. The definition is actually stored as a single BLOB. So, there's no need to "display the view name on every row because there will be only 1 row per view. Any wrapping is purely a function of whatever you're using to display the content of that one row,
sp_HelpText is one of "those" display tools. If you copy and past the blob of a definition to and edit window in SSMS, you perceive multiple rows/lines even though it's really just one with formatting characters embedded in it.
(n/a, expanded by later posting)
CORRECTION to my original table definition. We want to make sure SQL "knows" it doesn't actually have to do a sort. I accidentally left that off the original definition:
CREATE TABLE #text ( id int IDENTITY(1, 1) NOT NULL PRIMARY KEY, text_line varchar(max) NULL );
The table returned by the code literally has multiple rows returned in it. I'm not sure what else you're looking at. I understand that internally it's not stored in rows, but we, as people, are obviously not using the internal storage method, but ones amenable to humans.
If you want the CLOB definition -- it's stored as a CLOB, not a BLOB -- use OBJECT_DEFINITION().
What are you using for a tool to view the output of the code? Here's what I get in SSMS in the Grid mode. Just one row per view.
And, yeah... it's a "CLOB" not a "BLOB". I meant to type just LOB.
I see, you're not referring to sp_helptext but to internal tables. I didn't look at your code in any detail until now.
Yes, as I noted earlier, the internal views do indeed show it as a single CLOB. But, I was answering a specific q for the OP, based on what they wanted to know. For their output, an IDENTITY is needed.
Besides, you have to be very careful with the internal views because of inline comments using '--'. Copy the CLOB definition from the screen and try to run it, most of the time it will NOT work because comments get messed up.
I was taking a different approach and suggesting that sp_HelpText isn't the way to go. I know he said that's what he wanted but I'm thinking (based on his quest) that a single query with a sing list to maintain (could actually be in a separate table) might be handy.
Ok, makes sense, I get that. Most people just want to do that what they're trying to do quickly, not change it up, but it's worth a short.
But a view name is still not necessarily unique by itself, unless you're specifically restricting results to one schema. So, I would not rely on it being unique because it could break later when a view owned by a different schema is included in the results.
That's why I included the schema in the source list of views. If you wanted to enforce the 2 part naming convention, that would be easy enough to do.
hi
option another number 1 = SSMS Script Objects
option another number 2 = powershell Scripting with SMO plugging
