BenL's WebLog - All Comments

re:Maintaining SQL Server Databases

TrackBack — Sun, 10 Apr 2005 09:36:00 GMT

^_^,Pretty Good!

re: Maintaining SQL Server Databases

Ben Lower — Thu, 15 Jan 2004 16:50:00 GMT

Justin:
I am aware of the scripting feature. It is very nice. However, I've found that by starting with the script from the very beginning (not creating tables via Ent. Mgr.), I can really save time by copying the DDL code into my SPROC scripts.

Jerry:
Your comments reminded me of a nice MSDN article from 4/2003 that deals with automated creation of SQL code & using prefixes. (http://msdn.microsoft.com/msdnmag/issues/03/04/storedprocedures/default.aspx)

re: Maintaining SQL Server Databases

Jerry Pisk — Thu, 15 Jan 2004 07:27:00 GMT

I use a similar approach as well, except we use prefixes instead of fodlers so you can tell what objects you're working with (so our user stored procedure are all named up_module_subject_action). And we keep primary keys, indexes, foreign keys and constraints together with table scripts as the tables should never be created without those (it's just too error prone if you have to remember to manually create a primary key after you create a table).

The side effect of using script is that it keeps less experienced developers (read people who code using their mouse) out of the database ;)

re: Maintaining SQL Server Databases

John Bates — Thu, 15 Jan 2004 04:35:00 GMT

Very good idea; I use a similar method. The best benefit for me is that each SP, etc. is version controlled and can have a SCC-generated comment with the version of that script. Really handy when debugging a problem at a customer site - just check version numbers to see if they're running the latest scripts.

re: Maintaining SQL Server Databases

Justin — Thu, 15 Jan 2004 03:23:00 GMT

In Query Analyzer, if you have the Object Browser open, you can right click and have scripts generated for you too. Sounds very similiar but the QA method saves you the time of having to script the DDL yourself.