Get Sproc Code into a Table

Question

Hi I am trying to write all my sprocs into a table on sql server and I have an issue with that
What I am trying to do is

I am trying to create a new Table on my Database and feed the new table with the code of my Sprocs and my NewTable Structure is as follows
NewTable(ID, SchemaName, ObjectName, AlterSprocCode, CreateSprocCode)

To feed the above table, I am sourcing it from the following query

SELECT TOP 5 o.name, o.type, REPLACE(definition, 'CREATE PROCEDURE', 'ALTER PROCEDURE'), * FROM sys.sql_modules m
      INNER JOIN sys.sysobjects o ON
            m.object_id = o.id

How ever the issue is with the Code. When I execute the above statement It is executing fine and it is returning the Sproc Code. Issue is with the code being retrieved in a Single Line.

I dont want the Comment Lines to be Scripted in the Procedure (-------------)

Is there a solution for this

Answer 1

So are you trying to format the script in your Result window???????????????

---

Abdallah El-Chal, PMP

Answer 2

You're going to get the comments, but the carriage returns are in the code as well so if you needed to you could parse the comments out by eliminating everything starting with -- and ending with a carriage return

Consider using this instead of sysobjects

SELECT ROUTINE_DEFINITION
from INFORMATION_SCHEMA.ROUTINES

---

Chuck

Answer 3

Hi OldJeep Thanks for the comment, How can I parse the -- in my code with Carriage Return.

Answer 4

Hi OldJeep Thanks for the comment, How can I parse the -- in my code with Carriage Return.

Easiest way would be with a regex in a clr stored procedure.  Alternately you could loop through the strings using a pair of patindex statements to find the beginning of the comments and end of lines.   Not really a super clean way to do it.

Why does it matter if the comments are in there?  What are you actually trying to accomplish?

---

Chuck

Answer 5

Set Query results to text to see the code not in a single line.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Hi This New Table that I am going to create is Used by another automated process, That Automated process will take the AlterSprocCode column and will Execute it on Some Other DB, So I will have to come up with a Little cleaner code

Since my Statement is returning the code in a Single line, If I have a "--" or "----------------" or"===========" values in the Sproc code, from that place it will be a comment right. 

Answer 7

Hi Naomi, How to set the Query Results to Text, This is my Code

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION AS CreateSprocCode,
REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE', 'ALTER PROCEDURE') AS AlterSprocCode
from INFORMATION_SCHEMA.ROUTINES
Where ROUTINE_TYPE = 'PROCEDURE'

Answer 8

I meant in SSMS go to Query/Results To/To Text menu (or press Ctrl + T before running the query). However, this is only to view results in SSMS.

I don't understand what is the problem with keeping comments and carriage returns in the text of the SP? We can not remove them.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

I meant in SSMS go to Query/Results To/To Text menu (or press Ctrl + T before running the query). However, this is only to view results in SSMS.

I don't understand what is the problem with keeping comments anf carriage returns in the text of the SP? We can not remove them.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

The problem is that when you pass the field value to Exec or sp_executesql it ignores the embedded carriage returns and the effect is that the sql batch terminates at the first -- style comment

---

Chuck

Answer 10

Why we can not pass content as a variable? In this case it will not ignore the carriage returns.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

Out of curiosity - this other process that will execute the proc code.  What is it written in?  If you are building a .Net utility then you could more easily take care of this in the ,Net code. 

---

Chuck

Answer 12

I dont know much about that Application, But I think It was Written in C Sharp

Answer 13

Why we can not pass content as a variable? In this case it will not ignore the carriage returns.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

It seemed to when I tried it out.  The statement terminated at the first instance of --

---

Chuck

Answer 14

Why we can not pass content as a variable? In this case it will not ignore the carriage returns.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

It seemed to when I tried it out.  The statement terminated at the first instance of --

---

Chuck

Less complicated test - this worked. However when I was selecting the routine_definition it was not working - may have been a different issue

DECLARE @test varchar(max)

SET @Test = 'SELECT * --' + char(13) + 'from  sysobjects'

select @test
exec (@test)

---

Chuck

Answer 15

I have written the following SPROC and It is not throwing me any error but What its doing is It is Scripting Out all the Sprocs against Each SprocName


ALTER Procedure [dbo].[TestSprocCodeInsert]
AS
Begin

TRUNCATE TABLE dbo.SecurityDBSprocs
--Temp Table to Store Schema Name and Sproc Name
Create Table #temp(Sc Varchar(50), Spr Varchar(256), SPRF Varchar(256))
Insert INto #temp(Sc, Spr, SPRF)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME,  
'SP_HELPTEXT'+'  '+'['+ROUTINE_SCHEMA+'.'+ROUTINE_NAME+']'
from INFORMATION_SCHEMA.ROUTINES
Where ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_NAME NOT LIKE 'SP_%'

--SELECT * FROM #temp 
-- Declare Cursor to Store the End Result Into dbo.SecurityDBSprocs
DECLARE Cur Cursor FOR
SELECT Sc, Spr From #temp Order By Sc, Spr
DECLARE @Sc Varchar(50), @Spr Varchar(255)
OPEN Cur

FETCH NEXT FROM Cur INTO  @Sc, @Spr

declare @sql table (Script varchar(800))
DECLARE @Text1 Varchar(800)
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec @Text1 = sp_HelpText @Spr 
insert into @sql (Script) EXEC sp_HelpText @Spr

-- loop through the rows of @sql here using a cursor Cur1
DECLARE Cur1 Cursor FOR
SELECT Script From @Sql
DECLARE @Sql1 Varchar(800)

OPEN Cur1

FETCH NEXT FROM Cur1 INTO  @Sql1
WHILE (@@FETCH_STATUS = 0)
BEGIN
Insert INtO dbo.SecurityDBSprocs (SchemaName, SprocName, Script)
SELECT @Sc, @Spr, @Sql1
FETCH NEXT FROM Cur1 INTO  @Sql1
END
CLOSE Cur1
DEALLOCATE Cur1

FETCH NEXT from Cur into @Sc, @Spr


END
CLOSE Cur
DEALLOCATE Cur
DROP Table #temp


END