Friday, 9 March 2012

Script Out / Back Up All the Store Procedures

My lovely developers like to chanllenge my scripting skills. Today I was asked to restore one database on Dev server by using the backup taken from Prod. However, they wanted to keep all the store process in Dev. In order to avoid the store procs were removed during the dateabase restoring by prod backup,  I had to script them out and then run them after restoring completed. The following codes help me to do that in one go without repeating the same process.                                   

SET NOCOUNT ON
DECLARE @SP TABLE ( Id INT IDENTITY (1, 1),
                     Code VARCHAR (MAX) );

INSERT INTO @SP (Code)
SELECT 'IF OBJECT_ID(N''[' + schema_name(schema_id) + '].[' + Name + ']'') IS NOT NULL ' + char(13) + char(10) 
       + '    DROP PROCEDURE [' + schema_name(schema_id) + ' ].[' + Name + ']' + char(13) + char(10) 
       + ' GO' + char(13) + char(10)
       + OBJECT_DEFINITION(OBJECT_ID) + char(13) + char(10) + 'GO' + char(13) + char(10)
FROM   sys.procedures
WHERE  is_ms_shipped = 0;

DECLARE @ID AS INT;

DECLARE @SCRIPTS AS VARCHAR (MAX);

SET @ID = 1;

WHILE @ID <= (SELECT MAX(Id)
              FROM   @SP)
    BEGIN
        SELECT @SCRIPTS = Code
        FROM   @SP
        WHERE  Id = @ID;
           WHILE @SCRIPTS <> ''
              BEGIN
                  PRINT LEFT(@SCRIPTS, 8000);
                  SET @SCRIPTS = SUBSTRING(@SCRIPTS, 8001, LEN(@SCRIPTS));
               END
          SET @ID = @ID + 1;
    END

There is simple query  the query to see the definetions (scripts) for all the exsiting objects on the database.

   SELECT LEFT(o.name, 100) AS Object_Name,
                  o.type_desc,
                  m.definition
   FROM   sys.sql_modules        m
   INNER JOIN sys.objects  o
   ON m.object_id=o.object_id

No comments:

Post a Comment