Monthly Archives: December 2007

Cleaning up TempDB

In many significantly large production environments, the TempDB can regularly grow with little or no thought towards maintaining some control over the lifespan of tables. In most situations, TempDB is not used heavily enough to be a problem, but occasionally … Continue reading

Posted in SQL Server | 42 Comments

Data Dictionary built from the DB itself

Much like most developers, I hate doing documentation. It’s a boring chore, which must be done from time to time; but it’s tedious busy-work, and involves no imagination or creativity… So to ease the pain of creating SOME of the … Continue reading

Posted in SQL Server | Leave a comment

Audit DDL Operations

One of the great new features in SQL Server 2005 is the ability to create database triggers. Using this new functionality, you can create a table to store all DDL changes done to a given DB along with what the … Continue reading

Posted in SQL Server | 1 Comment

SQL Compare (Red-Gate Software)

When dealing with multiple databases (or DB environments such as Dev, Test, Prod), one of the biggest headaches a DBA can come across is keeping all of the DBs/Environments in sync. With SQL Server 2005 there is the new Database … Continue reading

Posted in Review, SQL Server | Leave a comment

Data Differences Among Tables

The following has been most useful in the past for me in updating lookup tables (hence the Stored Procedure name) but can be used for any table that has the same structure across multiple DBs. I’ll write a more in … Continue reading

Posted in SQL Server | Leave a comment

Record Count of all Tables

One of the simplest things that will come up constantly is trying to understand where your data is in your DB. And while not the MOST accurate way to determine this, one quick and dirty method is to get a … Continue reading

Posted in SQL Server | 2 Comments

Microsoft Patterns and Practices (yes, they are for SQL Server as well)

Microsoft’s Patterns and Practices for SQL Server is a great place to go and check out the proven MS ways to store, move, and access your data in a MS SQL Server environment. I won’t go into too much detail … Continue reading

Posted in SQL Server, Website | Leave a comment

Defrag Indexes

Fragmented Indexes, while not a common thought among DBAs and developers, can be one bottleneck in your DB performance. MS SQL Server includes three DBCC functions to help the DBA find these fragmented Indexes, and fix them: DBCC SHOWCONTIG DBCC … Continue reading

Posted in SQL Server | Leave a comment

Simple Encryption

With the closer integration of SQL Server and dotNET, this bit of functionality may well be obsolete, but looking through my history of saved scripts, I ran across a very simplistic Encryption routine I had created some time ago. Originally … Continue reading

Posted in SQL Server | Leave a comment

Replace text (in the data) in a database

Something that I came across recently as a task, that I felt might be interesting to share. This code combines a few things I have done previously (such as looking through system tables, parsing through data, and table variables) and … Continue reading

Posted in SQL Server | Leave a comment