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.
