Monthly Archives: October 2007

Comparing MS SQL Database Structures and Data

Filed under Code Garage, SQL

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.

AdeptSQL is my favorite, but RedGate SQL Compare is very good. And DBGhost is highly regarded for synchronizing db schemas (and data to a degree, I believe).

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


DECLARE @Rows as int


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
        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
        -- 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, ''
        FETCH NEXT FROM cCursor INTO @Table

    CLOSE cCursor
    DEALLOCATE cCursor

	-- now Scan all the Stored Procs and functions
        select lower(ROUTINE_NAME) from Information_Schema.Routines order by ROUTINE_NAME
    OPEN rCursor
    FETCH NEXT FROM rCursor INTO @Routine
        -- 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
        FETCH NEXT FROM rCursor INTO @Routine

    CLOSE rCursor
    DEALLOCATE rCursor

	-- now Scan all the Stored Procs and functions
        select lower(TABLE_NAME) from Information_Schema.Views order By TABLE_NAME
    OPEN vCursor
    FETCH NEXT FROM vCursor INTO @View
        -- 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
        FETCH NEXT FROM vCursor INTO @View

    CLOSE vCursor
    DEALLOCATE vCursor


-- 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)
	   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)
					left(Keys.CONSTRAINT_NAME,30) as IDXName, 
					left(Keys.COLUMN_NAME, 20) as ColumnName, 
					Left(RI.UNIQUE_CONSTRAINT_NAME, 20) as PrimaryKey, 
			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)

-- Handle all stored procs here
IF LEN(IsNull(@SPName, '')) > 0 BEGIN
	PRINT replicate('*', 60)
	PRINT replicate('*', 60)
	-- pull the entire text of the proc into a variable to let us print it nicely
	EXEC sp_helptext @SPName

-- 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


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.

The Ultimate in “Not Invented Here”

Filed under Rants

I was reading an article about the Chinese space program and it kinda reminded me of a few projects I’ve worked on.

Don’t get me wrong, working on a space program could possibly be the geek’s ultimate dream, and I’m all for space exploration. Mission to Mars, manned stations, space tourism, you name it.

But think about it this way:

They’re working on sending an exploratory mission up sometime in 2012, and then a manned mission around 2024. This is pretty cool in and of itself.

But then, somebody else has already done it.

Those on that project would seem to be in a pretty thankless position.

  • If they succeed, after years of work, they’ll get a “Congratulations, you’ve done something that was done almost 50 years ago, again.”
  • And if they fail, they’ll get “Somebody else did this 50 years ago, you’ve got 50 years worth of modern tech behind you and you still couldn’t pull it off.”

Makes me wonder how many IT projects wind up in that same boat.

It also seems that the safest way out for everyone involved is for the project to simply get canceled. Maybe that’s why so many are.

And therein would be the benefits of being first, even with the inevitable casualties of diving headlong into uncharted waters, literally or metaphorically.

A Bit of a New Look, or Fun With CSS

Filed under Blogging, CSS

The formatting of the code snippets I post from time to time have always bothered me.

They didn’t wrap, which is good (wrapping code just annoys the hell out of me), but they trailed off the edge and just appear to get cut off.

If you selected the text, it was all there and could be copied to the clipboard, but… well…. yuck.

Anyway, I finally figured out how to change the CSS such that you get a nice little scrolling window for the snippet.

I should point to Scott Hanselman’s blog as the source of the example I found, but I did tweak it a bit.

Here’s an example:

Public Function AppVersion$()
   '---- Get the Current Application file's "Full" version info
   Dim p$

   If IsIDE() Then
      '---- just pull from the App values (because the version resource
      '     isn't available
      AppVersion$ = App.Major & "." & App.Minor & ".0." & App.Revision
      '---- get from the VersionInfo resource, will include the real revision and build numbers
      AppVersion$ = GetFileVersion$(App.Path & "\" & App.EXEName)
   End If
End Function

Since this is purely a CSS thing, it works backwards in all my old posts, and it appears to be (so far) cross browser compatible.

Here’s the style (I’ve enclosed it in a STYLE tag so you can embed it directly in a html file, otherwise, just use the PRE definition).

<style type="text/css">
pre {
	border: 1px solid #e3a83d;
	border-left-width: 2px;
	background-color: #444;
	padding: 1em;
	margin: 2em;
	line-height: 1.2em;
	overflow: auto;
	width: 90%
	font-size: small;
	color: white;
	font-family: consolas, "Courier New", courier, monospace;

Yeah, it’s probably pretty minor stuff for all the CSS genii out there, but I’m still surprises with what you can do with a little CSS. Makes me really want to get into the whole WPF thing. 

2^16 Gives Excel 2007 Heartburn

Filed under Uncategorized

And in the “Oow, I’m glad I’m not in that department” department.

The latest Woody’s Office Watch reports that Excel 2007 can’t format 2^16 or 2^16-1 properly, instead deciding to show it as “100,000”


Heh. It’s not too bad. According to Microsoft, the bug only affects 12 numbers (or is it 2, I’m not sure, but the quote says 12) out of a virtually infinite number space. From Woody’s; “They (Microsoft)point out that Excel can use ” 9.214*10^18 different floating point numbers ” but the bug only affects 12 of that incredibly large number of possibilities”.

Oh man. Can I use that one with my next off-by-one mistake? (hey, I don’t make ’em much anymore but still…).

Those Pesky Internets

Filed under Uncategorized

Well, through an amazing set of circumstances, including spam filters gone awry, old email addresses that were no longer valid, too many years and too few vacations, somehow my domain name expired without so much as a whimper (that is, until I started getting IM from people and email through different accounts that went something like, “Hey, where’s your domain at, dude!?”).

Anyway, things are back up now, with all the pointers, DNS entries and hostnames back in place.

Also, I’ve obtained the domains and, so they also point here, with all the mail and DNS forwarding goodness that is this series of tubes.

VB and Dirty Laundry

Filed under Rants, VB Feng Shui

No, I don’t do Don Henley impersonations. But I was starting a load of laundry the other day and something struck me. Once I’d recovered and put the broom back where it was supposed to be, I realized something.

Take your standard, run of the mill washing machine. It’s essentially a variable speed motor, a few belts and pulleys, a water pump, and big cog timer wheel that sort of looks like the sheet music for a player piano*.

These things will run several times a week, for years on end and only rarely have any issues. Even then, the most likely problems are things like belts wearing out, bearings getting squeaky, or the timer cogs breaking off causing cycles not to start or stop properly. Further, I’d be willing to bet that just about anyone can set one up, plug it it, connect the 2 water hoses and the drain line, and be doing laundry within 30 minutes to an hour, and just about never think about any of that “configuration and setup” again till they move. 

Compare that, then, to the lastest washing machines, with “steam” cleaning, LCD consoles, touchscreens, dozens of “operating modes”, delay start timers, and even internet access.

I love gadgets, but seriously, does being internet-enabled or using an LCD touch screen get my clothes any cleaner, or prevent me from actually having to lug my laundry to the utility room? Do they actually save real people any time whatsoever over a 200$ (or cheaper) model with knobs instead of touchscreens and mechanical timers instead of microchips? Is this really progress?

I gotta stop hitting myself with brooms.

* and yes, that’s an oversimplification, for all you washing machine enthusiasts out there.

Digitally Signing a Word 2007 Document

Filed under Office, Security

I originally wrote about digital signatures in Office documents way back here, so check there for more information. But I just stumbled across something with Word 2007 and document signing (including signing templates) that had me scratching my head for a minute.

Word, in it’s shiny new 2007 skin, now has a nifty little feature to add a signature to a document (or template) right on the…um… what they hell you do call this button?


Anyway, under “Prepare”, you’ll see this:


The Add a Digital Signature lets you sign the document right there. Which is great.

Except for one thing.  That signature is not the same as this one:


The former actually signs the document, whereas the latter signs the VBA code contained in the document.

If you don’t believe me, sign a document using the Prepare menu item, then check the signature using the VBA/Tools/Digital Signature menu item. Then sigh and weep.

So, what does that matter, you ask?

Well, in terms of checking the validity of macro code in a document, from what I can tell so far, the signature on the document isn’t checked, only the signature on the VBA code. From a macro/VBA standpoint, signing the document is pretty useless.

I’m still hunting for a way to automate the signing of DOC and DOT files (such as the SIGNTOOL.EXE utility for signing DLL’s and EXE’s). That would make the whole process much more convenient, not to mention enabling it to be built into a normal build process.