1. Create a new SQL Server DB called "MSSQL_Code_Repository"
2. Create a new Table called "CodeHistory"
CREATE TABLE [dbo].[CodeHistory](
[CodeHistoryID] [int] IDENTITY(1,1) NOT NULL,
[InsertDate] [datetime] NULL CONSTRAINT [DF__CodeHistory] DEFAULT (getdate()),
[DBName] [varchar](50) NULL,
[ObjectType] [varchar](50) NULL,
[ObjectName] [varchar](100) NULL,
[ObjectText] [varchar](8000) NULL,
CONSTRAINT [PK__CodeHistory] PRIMARY KEY CLUSTERED
(
[CodeHistoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
3. Create a new Table called "CodeOutput"
CREATE TABLE [dbo].[CodeOutput](
[CodeText] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
4. Create a new Stored Procedure called "Extractor"
CREATE PROCEDURE [dbo].[Extractor] @DBName NVARCHAR(50)
AS
SET NOCOUNT ON
--Source: www.TechDevOps.com - MSSQLCodeRepo tool
--Exec dbo.Extractor <dbname>
--See: dbo.Viewer for viewing code in proper format.
--Check if Extractor has execute today. If so, raiseerror and exit because only once a day supported
Declare @LastDate datetime
Set @LastDate = (select max(insertdate) from dbo.CodeHistory)
If @LastDate > DATEADD(d,0,DATEDIFF(d,0,GETDATE()))
Begin
Select 'Exiting due to Extractor has already been ran today.' as 'Message'
Return
End
DECLARE @dynSQL NVARCHAR(4000)
Set @dynSQL = ''
Declare @StartProcess varchar(50)
Declare @EndProcess varchar(50)
Set @StartProcess = ''''+ @DBName + '''' + ' Code Extract started.'
Print @StartProcess
Print ''
Print 'Stored Procedures starting...'
SET @dynSQL = 'INSERT INTO CodeHistory(DBName,ObjectType,ObjectName,ObjectText)
SELECT ''' + @DBName + ''',' + 'so.type'+ ',so.name, sc.text FROM ' + @DBName + '.dbo.sysobjects so
INNER JOIN ' + @DBName + '.dbo.syscomments sc ON so.id = sc.id
WHERE so.name not like ''dt_%'' and so.type = ''p''' --Stored Procedures
EXEC dbo.sp_executesql @dynSQL
Print 'Stored Procedures complete.'
Set @dynSQL = ''
Print ''
Print 'Scalar Functions starting...'
SET @dynSQL = 'INSERT INTO CodeHistory(DBName,ObjectType,ObjectName,ObjectText)
SELECT ''' + @DBName + ''',' + 'so.type'+ ',so.name, sc.text FROM ' + @DBName + '.dbo.sysobjects so
INNER JOIN ' + @DBName + '.dbo.syscomments sc ON so.id = sc.id
WHERE so.type = ''fn''' --Scalar Functions
EXEC dbo.sp_executesql @dynSQL
Print 'Scalar Functions complete.'
Set @dynSQL = ''
Print ''
Print 'Table Functions starting...'
SET @dynSQL = 'INSERT INTO CodeHistory(DBName,ObjectType,ObjectName,ObjectText)
SELECT ''' + @DBName + ''',' + 'so.type'+ ',so.name, sc.text FROM ' + @DBName + '.dbo.sysobjects so
INNER JOIN ' + @DBName + '.dbo.syscomments sc ON so.id = sc.id
WHERE so.type = ''tf''' --Table Functions
EXEC dbo.sp_executesql @dynSQL
Print 'Table Functions complete.'
Set @dynSQL = ''
Print ''
Print 'Inline Table-Functions starting...'
SET @dynSQL = 'INSERT INTO CodeHistory(DBName,ObjectType,ObjectName,ObjectText)
SELECT ''' + @DBName + ''',' + 'so.type'+ ',so.name, sc.text FROM ' + @DBName + '.dbo.sysobjects so
INNER JOIN ' + @DBName + '.dbo.syscomments sc ON so.id = sc.id
WHERE so.type = ''if''' --Inline Table-Functions
EXEC dbo.sp_executesql @dynSQL
Print 'Inline Table-Functions complete.'
Set @dynSQL = ''
Print ''
Print 'Views starting...'
SET @dynSQL = 'INSERT INTO CodeHistory(DBName,ObjectType,ObjectName,ObjectText)
SELECT ''' + @DBName + ''',' + 'so.type'+ ',so.name, sc.text FROM ' + @DBName + '.dbo.sysobjects so
INNER JOIN ' + @DBName + '.dbo.syscomments sc ON so.id = sc.id
WHERE so.name not like ''sys%'' and so.type = ''v''' --Views
EXEC dbo.sp_executesql @dynSQL
Print 'Views complete.'
Set @dynSQL = ''
Print ''
Print 'Triggers starting...'
SET @dynSQL = 'INSERT INTO CodeHistory(DBName,ObjectType,ObjectName,ObjectText)
SELECT ''' + @DBName + ''',' + 'so.type'+ ',so.name, sc.text FROM ' + @DBName + '.dbo.sysobjects so
INNER JOIN ' + @DBName + '.dbo.syscomments sc ON so.id = sc.id
WHERE so.type = ''tr''' --Triggers
EXEC dbo.sp_executesql @dynSQL
Print 'Triggers complete.'
Set @dynSQL = ''
Set @EndProcess = ''''+ @DBName + '''' + ' Code Extract complete.'
Print ''
Print @EndProcess
GO
5. Create a new Stored Procedure called "History"
CREATE procedure [dbo].[History]
@ObjectName varchar(100)
as
set nocount on
--Source: www.TechDevOps.com - MSSQLCodeRepo tool
select distinct ObjectName as 'Object', InsertDate as 'Scripted Date',
case ObjectType
when 'P' then 'Stored Procedure'
when 'V' then 'View'
when 'TR' then 'Trigger'
when 'TF' then 'Table Function'
when 'FN' then 'Scalar Function'
end as 'Type'
from dbo.CodeHistory
where objectname = @ObjectName
order by insertdate
GO
6. Create a new Stored Procedure called "Viewer"
CREATE Procedure [dbo].[Viewer]
@DBName varchar(50),
@ObjectName varchar (250),
@ExtractYear char(4),
@ExtractMonth char(2),
@ExtractDay char(2)
AS
--Source: www.TechDevOps.com - MSSQLCodeRepo tool
--Exports to a file via xp_cmdshell
--Sample execution: exec dbo.Viewer '<dbname>', '<objectname>', '<year>', '<month>', '<day>'
Declare @long varchar(max)
Declare @Code varchar(max)
Declare @DBMailSubject varchar(150)
Declare @RecCount int
Set @Code = ''
Set @Long = ''
SELECT @RecCount=count(*) FROM dbo.CodeHistory where DBName = @DBName AND ObjectName = @ObjectName AND
DATEPART(YEAR,insertdate) = @ExtractYear AND
DATEPART(MONTH,insertdate) = @ExtractMonth AND
DATEPART(DAY,insertdate) = @ExtractDay
IF @RecCount = 0
Begin
Select 'No code extract exists for that Object and Date.' as 'Message'
Return
End
Set @DBMailSubject = (Select top 1 InsertDate
FROM dbo.CodeHistory where DBName = @DBName AND ObjectName = @ObjectName AND
DATEPART(YEAR,insertdate) = @ExtractYear AND
DATEPART(MONTH,insertdate) = @ExtractMonth AND
DATEPART(DAY,insertdate) = @ExtractDay)
Set @DBMailSubject = @ObjectName + ' - ' + @DBMailSubject
DECLARE codehistory CURSOR FOR
SELECT ObjectText as 'Code' FROM dbo.CodeHistory where DBName = @DBName AND ObjectName = @ObjectName AND
DATEPART(YEAR,insertdate) = @ExtractYear AND
DATEPART(MONTH,insertdate) = @ExtractMonth AND
DATEPART(DAY,insertdate) = @ExtractDay
OPEN codehistory
FETCH NEXT FROM codehistory INTO @long
WHILE @@FETCH_STATUS = 0
BEGIN
--If @Long is NOT NULL and @Long <> '' Begin
Set @Code = @Code + '' + @Long
--End
Set @Long = ''
FETCH NEXT FROM codehistory
INTO @long
END
Truncate table dbo.CodeOutput
Insert into dbo.CodeOutput(CodeText) Select @Code
--Select @Code FOR XML PATH('') --This option displays the full stored procedure BUT the XML is not well-formed
--Need to export as a file in order to get the full stored procedure.
--(If use just Select @Code, SSMS has a limit on display for large varchar(max) and thus cannot see the full stored procedure as a result set)
declare @sql varchar(8000)
select @sql = 'bcp "select * from [MSSQL_Code_Repository].dbo.CodeOutput" queryout c:\code_repo_file.txt -c -t, -T -Sservername'
exec master..xp_cmdshell @sql
CLOSE codehistory
DEALLOCATE codehistory
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ,
@recipients=,
@subject = @DBMailSubject,
@body = '',
@body_format = 'HTML',
@file_attachments='C:\code_repo_file.txt' ;
GO
7. Create a SQL Mail Profile to email the extracted object (optional)
If you do not require this then comment out the email code located in the stored procedure called "Viewer"
Run Extractor:
Exec [dbo].[Extractor] 'DBNameHere'
Run Viewer:
Exec [dbo].[Viewer] 'DBNameHere', 'ObjectNameHere', 'YearHere', 'MonthHere', 'DayHere'
View History:
Exec [dbo].[History] 'ObjectNameHere'
Note:
1. Use at own risk.
2. Test out your own extracts and ensure matches the source.
3. Please report issues if any discovered.
4. Reference source www.TechDevOps.com - MSSQLCodeRepo tool
|
|
|
|
|
|