asked on

Get Stored Procedure code into a Table

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

SJCFL-Admin

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...

karon1980

ASKER

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.

SJCFL-Admin

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

karon1980

ASKER

Can the Source Result set be Stored into a Table. My Specific requirement is to Store the scripts in a table.

SJCFL-Admin

No, this task is geared towards direct execution in another environment.

Sorry...

karon1980

ASKER

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

wdosanjos

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.

ASKER CERTIFIED SOLUTION

SJCFL-Admin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SJCFL-Admin

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 ;-)