Monday, October 12, 2009

SQL Database Documentation Tool

Database documentation is one of those fun things that everyone loves to do, ok not really. It is usually difficult and hard to do and if there are changes to a table schema or stored procedure, you can't really query the database to ask it what was changed.

Along comes Red Gate with their SQL Doc utility to make our lives easier in the database world anyway. I gave this a try earlier this year and documented every database on every server. The cool part is that it'll show you all the dependencies between objects and generate scripts for all the objects to re-create them. You can output the documentation in HTML format, Word Document format, or my favorite a CHM help file. The CHM help file seemed to be the most useful to me as I could easily search on every item in the database on the server. The process doesn't take an extraordinary amount of time to compile either. So when you first design a database, you can create a documentation snapshot. Then you can snapshot the documentation again after changes or whenever you need to troubleshoot or want to see the latest info.

It would be really cool if the utility could just tell you the changes from the original configuration and who changed it, but that will require some type of auditing. However, you could use a document differencing utility and compare version of documentation to find the deltas.

Go check out the utility and see what you think. Red Gate offers a 14 day trial to test drive it.

Saturday, October 10, 2009

Why you should specify column names in your T-SQL SELECT statements

This is one of those things that irks me and I know the reasons to explicitly specify the column names in your select statements. One is to limit the data to exactly what you need and to also speed up compilation of the query because it has to be ultimately returned to columns on the backend of the database engine. Then if you end up with schema changes, it just causes all sorts of problems. However, I just saw a blog post from Aaron Bertrand on this subject and he does a much better job of explaining it in a very straightforward manner, go check it out.

Code Camp Presentation info coming soon...

To all who saw my presentation at Code Camp on Automating SQL Maintenance and Backups today or want to see it. I will try to get the presentation and code posted within the next couple of days, just need to organize it and write up some additional info for you. Check back soon...