Wednesday, August 23, 2006

As promised in my article on upgrading database schemas, I will show you the trick that I have learned when I was preparing SQL database upgrade scripts for my databases (that is before I thought on using some automation such as SQL Delta).

The fastes way I have found to get the database schema from a Microsoft SQL Server 2000 is to use the Enterprise Manager. Just navigate to a database you want, select the Tables node and select the tables you want to get schema for (you can select multiple tables using either shift or control key). As an example I have selected two tables of MyGallery database:

Having selected the desired tables use the context menu and select Copy or use the ctrl+c shortcut. Now open some kind of text editor such as Notepad. Paste the content of a clipboard and what you get is something like:

Now that is very cool isn't it? I have used this technique fo comparing two databases some time ago. Just get the schemas from two databases, save them to files on your disk and use some text diff tool.

Far from perfect but if you have no tool this is what you got. One more thing to mention is that you can use this copy and past mechanism also with stored procedures.

kick it on