Get Stored Procedure code into a Table

karon1980
karon1980 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you are looking for a way to recreate your system, there is a wizzard that can be used to script out the procedures and the relevant permissions.  Saving the output of this wizzard is a much better option for easily recreating or porting your procedures.

The only reason I can think of for wanting to remove the comments is that they caused a problem when you try to execute the recreation as one huge string...  The procedure not being scripted as nicely as from the wizzard...  

But if I am wrong and there is another reason, please let me know...

Author

Commented:
Hi Here is my Source Query,

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'

The AlterSprocCode column which contains the Alter Procedure Code for the Sproc Will be picked up another Automated process from this table and will be Executed in another Database.

This Automated Program will Check for Some Column names and Stuff from the Sproc Code.. And After that It Will Execute.
Long term, I think you would be better served using Visual Studio Integration Services Services Project for this.

Visual Studio 2008

Create New Integregration Services Project

New Project

Integration Services Project

It will open a blank Package.dtsk [Design] Window

Drag over a 'Transfer Sql Server Objects task'

Double Click the Task

Expand the Objects to Copy

   Procedures allow you to specify all or a subset list
   
   Plus you can specify a number of other categories at the same time

And the package can be run manually at first and then automated via a SQL Server Agent Job
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Can the Source Result set be Stored into a Table. My Specific requirement is to Store the scripts in a table.
No, this task is geared towards direct execution in another environment.

Sorry...

Author

Commented:
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
Top Expert 2011

Commented:
sys.sql_modules.definition has the store procedure content properly formated (it's not a single line).  I guess you are making that assumption based on the way SSMS displays the column value by default.  Change to Results to Text (Ctrl+T) in SSMS and rerun the query to verify.
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )

will find find position of '--' where comment starts so something like

      CHARINDEX ('--',@yourretrievedline,1 )

      substring(line, 1, value returned by charindex)  


 if '--' was found will remove the comment following on the line
And many thanks for info on the multi text line. I had not realized that :-)  

But I can't say that I'd still want to code my own deployment software.  There are a number of inexpensive Sql compare packages. I think I'd pick up one of them as they would also provide other advantages...

But who knows, maybe I will see your name on a new offering ;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial