If you’ve messed with SQL much, invariably, you get into a situation where you have to ask “What has changed in the database from the last version to this version?”
There’s some good tools out there for this, to be sure.
But sometimes you want (or need) to “roll your own”.
So, how would you do that?
First, the criteria:
- I need to be able to compare both schema and some “seed data” in certain tables (for instance, pre-populated lookup tables, etc)
- I’d like to see any significant changes, but everyone’s definition of significant can be different.
- I’d like the changes to be “easy” to read. They need to pop out if possible
Next, the display.
Well, there’s plenty of file comparison utilities out there, and my favorite is Araxis, so why not just use it to do the “delta-generation” heavy lifting.
I’ll need two text files to compare, the “before” version and the after.
I need them grouped by schema object type (stored-procs in one section, table definitions in another, data values in another, etc).
And I need them ordered consistently so that any changes are easy to spot.
I ended up a with a stored proc that, at least right now, does most of what I need.
The basic idea is that you call DBOutput, and it then calls itself recursively to resolve and output all the various elements of the database that you might be interested in comparing. Why a recursive procedure instead of several separate procs? No earth-shattering reasons. It’s just easier to deal with one proc for this sort of thing that several separate ones.
The routine itself pretty simply, and it doesn’t yet have to resort to directly reading the system tables for any information. It makes heavy use of the INFORMATION_SCHEMA views to retrieve most information. It should run under MSSQL 2000, MSDE, 2005 Express and 2005, but I haven’t tested it under the 2000 variants.
Probably most notable is the use of sp_helptext to retrieve the actual full definition text of stored procs and views.
You’ll have to excuse the formatting, pasting into HTML seems to have messed up my nice tabs.
ALTER PROCEDURE [dbo].[DBOutput] ( @TableName sysname = null, @SPName sysname = null, @ViewName sysname = null ) AS DECLARE @Rows as int SET NOCOUNT ON if len(IsNull(@TableName, '')) = 0 and Len(IsNull(@SPName,'')) = 0 and Len(IsNull(@ViewName,'')) = 0 BEGIN -- do all tables declare @Table sysname declare @Routine sysname declare @View sysname declare @i int DECLARE cCursor CURSOR FOR select lower(Table_Name) from Information_Schema.Tables where table_type = 'base table' order by Table_Name OPEN cCursor FETCH NEXT FROM cCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN -- check for tables we don't care about if @Table in ('sysdiagrams' ) -- do nothing set @i = 0 else if left(@Table,2) in ('ms', 'xx') set @i = 0 else if left(@Table,3) in ('sys') set @i = 0 else if left(@Table,6) in ('dtprop') set @i = 0 else BEGIN exec DBOutput @Table, '' END FETCH NEXT FROM cCursor INTO @Table END CLOSE cCursor DEALLOCATE cCursor -- now Scan all the Stored Procs and functions DECLARE rCursor CURSOR FOR select lower(ROUTINE_NAME) from Information_Schema.Routines order by ROUTINE_NAME OPEN rCursor FETCH NEXT FROM rCursor INTO @Routine WHILE @@FETCH_STATUS = 0 BEGIN -- check for Stored Procs and functions we don't care about if @Routine in ('' ) -- do nothing set @i = 0 else if left(@Routine, 3) in ('sp_', 'fn_') set @i = 0 else BEGIN exec DBOutput '', @Routine END FETCH NEXT FROM rCursor INTO @Routine END CLOSE rCursor DEALLOCATE rCursor -- now Scan all the Stored Procs and functions DECLARE vCursor CURSOR FOR select lower(TABLE_NAME) from Information_Schema.Views order By TABLE_NAME OPEN vCursor FETCH NEXT FROM vCursor INTO @View WHILE @@FETCH_STATUS = 0 BEGIN -- check for Stored Procs and functions we don't care about if @View in ('' ) -- do nothing set @i = 0 else if left(@View, 3) in ('sp_', 'fn_') set @i = 0 else BEGIN exec DBOutput '', '', @View END FETCH NEXT FROM vCursor INTO @View END CLOSE vCursor DEALLOCATE vCursor RETURN END -- handle a single table recursively IF Len(@TableName) > 0 BEGIN DECLARE @PrimaryKeyColumn nvarchar(64) DECLARE @sql nvarchar(1000) DECLARE @Parms nvarchar(1000) DECLARE @Temp sysname -- Primary key info (we're not using yet) SELECT @PrimaryKeyColumn = [COLUMN_NAME] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE [TABLE_NAME] = @TableName -- dump a list of the columns, with pertinent info PRINT replicate('*', 60) PRINT replicate('*', 60) PRINT 'TABLE: ' + @TableName EXEC ('SELECT ORDINAL_POSITION as ColNum, left(COLUMN_NAME, 20) as ColName, left(DATA_TYPE, 20) as DataType, Left(COLUMN_DEFAULT, 20) as DefaultValue, CHARACTER_MAXIMUM_LENGTH as Len, IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS WHERE Table_name = ''' + @TableName + ''' order by ColNum') -- dump a list of the indexes, with pertinent info -- also includes RI information associated with the keys (if any) EXEC ('SELECT Keys.ORDINAL_POSITION as KeyNum, left(Keys.CONSTRAINT_NAME,30) as IDXName, left(Keys.COLUMN_NAME, 20) as ColumnName, Left(RI.UNIQUE_CONSTRAINT_NAME, 20) as PrimaryKey, RI.Match_Option, RI.Update_Rule, RI.Delete_Rule, CHK.Check_Clause FROM information_schema.Key_Column_Usage as Keys left outer join information_schema.referential_constraints as ri on keys.Constraint_Name = ri.Constraint_Name left outer join information_schema.check_constraints as chk on keys.Constraint_name = chk.Constraint_Name WHERE Table_name = ''' + @TableName + ''' order by KeyNum, IDXName') -- check if there's any data in the table. If so, dump it all set @SQL = 'SELECT @Cnt = Count(*) FROM ' + @TableName set @parms = '@Cnt int OUTPUT' EXEC sp_executesql @SQL, @Parms, @Cnt = @Rows OUTPUT IF @Rows > 0 BEGIN PRINT replicate('~', 60) PRINT 'TABLE DATA FOR:' + @TableName EXEC ('SELECT * FROM ' + @TableName) END PRINT '' PRINT '' END -- Handle all stored procs here IF LEN(IsNull(@SPName, '')) > 0 BEGIN PRINT replicate('*', 60) PRINT replicate('*', 60) PRINT 'STORED PROCEDURE/FUNCTION: ' + @SPName -- pull the entire text of the proc into a variable to let us print it nicely EXEC sp_helptext @SPName PRINT '' PRINT '' END -- Handle all Views IF LEN(IsNull(@ViewName, '')) > 0 BEGIN PRINT replicate('*', 60) PRINT replicate('*', 60) PRINT 'VIEW: ' + @ViewName -- pull the entire text of the proc into a variable to let us print it nicely EXEC sp_helptext @ViewName PRINT '' PRINT '' END SET NOCOUNT OFF
Basically, you just open up Enterprise Manager, and exec DBOutput. It’s best to put the results window in TEXT mode as opposed to grid mode. Then you can simply save the output to a text file and use your favorite file compare to get a delta. All the PRINTs are there just to make things a little more legible in a typical “text editor” view.
Let me know what you think, or if there’s additional bits to report that might be useful.
Also, I plan to update this script over time, and I’ll post the updates here.