Tracking SQL Server procedure and function changes

This Stack Overflow question inspired me to create a couple batch files today:

1. gen_all.cmd (creates one text file for each stored procedure and function in a database)

@echo off sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME NOT LIKE 'dt_%%' AND (ROUTINE_TYPE = 'PROCEDURE' OR ROUTINE_TYPE = 'FUNCTION')" -o "sp_list.txt" for /f %%a in (sp_list.txt) do sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+ROUTINE_NAME+N']'')) DROP '+ROUTINE_TYPE+N' [dbo].['+ROUTINE_NAME+']' FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a' UNION ALL SELECT N'GO' UNION ALL select [text] from sysobjects join syscomments on sysobjects.id = syscomments.id where sysobjects.[name] = '%%a' UNION ALL SELECT N'GO'" -o "ProcsAndFuncs\%%a.sql" del sp_list.txt

2. gen_since.cmd (same as gen_all.cmd, but takes a date/time parameter and only generates files for procs/funcs that were created or altered since the specified date/time)

@echo off sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME NOT LIKE 'dt_%%' AND (ROUTINE_TYPE = 'PROCEDURE' OR ROUTINE_TYPE = 'FUNCTION') AND (CREATED >= convert(datetime,'%1') OR LAST_ALTERED >= convert(datetime,'%1'))" -o "sp_list.txt" for /f %%a in (sp_list.txt) do sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+ROUTINE_NAME+N']'')) DROP '+ROUTINE_TYPE+N' [dbo].['+ROUTINE_NAME+']' FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a' UNION ALL SELECT N'GO' UNION ALL select [text] from sysobjects join syscomments on sysobjects.id = syscomments.id where sysobjects.[name] = '%%a' UNION ALL SELECT N'GO'" -o "ProcsAndFuncs\%%a.sql" del sp_list.txt

Usage (to generate any files altered on or after Jun 5th):

gen_since 06/05/2010

I know there are some slick database change management tools out there, both OSS (Tarantino) and commercial (Redgate SQL Compare)… but I’m going to try using this simple batch file for a while as a nice, mindless way to save my DB objects as files that can be tracked in source control.