Script para guardar procedimientos almacenados en SQL Server

Con los siguientes scripts podremos generar una copia de los procedimientos almacenados (sp) de nuestra base de datos SQL Server (versiones 2008, 2012, 2014 o 2016) a la que llamaremos [BD_TEST], y almacenarlos, un registro con la estructura de cada sp, en una tabla dentro de una base de datos a la que llamaremos [BD_AUDITORIA], es una alternativa a la generación manual que se hace usando el SSMS.

Tabla storedprocedures

Crearemos una tabla de nombre storedprocedures base de datos de auditoria

USE [BD_AUDITORIA]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[storedprocedures](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[sp] [text] NULL, -- Estructura del sp
	[fecha] [datetime] NULL, -- Fecha de registro
	[db] [varchar](50) NULL, -- Nombre de la BD
 CONSTRAINT [PK_h_storesprocedures] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Creación de script

Usaremos el siguiente script, que crea una tabla de tipo variable para almacenar en memoria la estructura de los procedimientos almacenados de la base de datos BD_TEST, para luego guardarlos en la tabla storedprocedure de la base de datos BD_AUDITORIA

USE BD_TEST
GO

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

INSERT INTO @Test (Code)
SELECT 'IF object_ID(N''[' + schema_name(schema_id) + '].[' + Name + ']'') IS NOT NULL
          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 @lnCurrent INT, @lnMax INT
DECLARE @LongName VARCHAR(MAX)

SELECT @lnMax = MAX(Id) FROM @Test
SET @lnCurrent = 1
WHILE @lnCurrent <= @lnMax
      BEGIN
            SELECT @LongName = Code FROM @Test WHERE Id = @lnCurrent
            WHILE @LongName <> ''
               BEGIN
                   PRINT LEFT(@LongName,8000)
                   SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName))
               END
            SET @lnCurrent = @lnCurrent + 1
      END

INSERT INTO BD_AUDITORIA.dbo.storedprocedures (sp, fecha, db)
SELECT code, GETDATE(), DB_NAME() from @Test

 
Usamos las funciones GETDATE() y DB_NAME para obtener la fecha/hora y nombre de base de datos.

Si se desea automatizar su ejecución, se puede crear un Job que lo realice cada cierto periodo de tiempo.