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 due to non-ideal code, many tables are created and left with no regular cleanup.

While I am a proponent of using Table Variables over Temp Tables, the fact remains that many developers, and a significant number of DBAs use Temp Tables, and it is left in the hands of the DBA to deal with any complications caused by this.

The most obvious flaw in using Temp Tables in a 24/7 Productions Environment, is that if your SQL Server box never reboots, your TempDB never gets cleaned out (unless it’s by the code that created the Temp Tables, and that never happens. To deal with this issue, I have created a SP that runs once an hour, deleting Temp Tables that are more than 12 hours old (unless they reside in a Table (TempTableToKeep) that I use to store the names of tables I want to keep, and the date/time to finally delete them.

The code:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N’[dbo].[TempTableToKeep]‘) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
DROP TABLE [dbo].[TempTableToKeep]
GO
CREATE TABLE [dbo].[TempTableToKeep] (
[TempTable] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateToDelete] [datetime] NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = N’sp_DropTempTables’ AND type = ‘P’)
DROP PROCEDURE sp_DropTempTables
GO
CREATE PROCEDURE sp_DropTempTables
AS
DECLARE @Cursor AS CURSOR
DECLARE @Name AS VARCHAR(100)
DECLARE @TableName AS SYSNAME
DECLARE @Owner AS VARCHAR(100)
DECLARE @SQL AS NVARCHAR(200)
SET @Cursor = CURSOR SCROLL FOR
SELECT tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.*
FROM TempTableToKeep
RIGHT OUTER JOIN tempdb.dbo.sysobjects ON TempTableToKeep.TempTable = tempdb.dbo.sysobjects.name
WHERE ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete < GETDATE())) OR
((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete IS NULL))
OPEN @Cursor
FETCH FIRST FROM @Cursor
INTO @Name, @Owner
WHILE (@@FETCH_STATUS = 0)
BEGIN
If (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name = @Name AND type = ‘U’)
BEGIN
SET @SQL = ‘DROP TABLE tempdb..’ + @Name
–PRINT @SQL
EXECUTE sp_executesql @SQL
END
FETCH NEXT FROM @Cursor
INTO @Name
END
END
CLOSE @Cursor
DEALLOCATE @Cursor
GO

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 documentation associated with the role of a DBA, I created (through the use of some code I found online, and modified) the following script.

This code uses Extended Properties of Fields (can be found while in design of a Table, the Description Property of each field) and Tables (can be modified using the following script).

Using these Extended Properties along with other information that can be found in the systables, I create a table variable storing all information about each field and table in the Database.

The code (to add Extended Properties to the table):
DECLARE @t VARCHAR(128)
DECLARE @v sql_variant
SET @t = ‘TableName’
SET @v = N’Long Description fo what the table does’
EXEC sp_dropextendedproperty ‘MS_Description’, ’schema’, dbo, N’table’, @t, null, null;
EXEC sp_addextendedproperty N’MS_Description’, @v, N’user’, N’dbo’,N’table’, @t, NULL, NULL

The code (to run the DataDictionary):
DECLARE @Columns TABLE
(
TABLE_TYPE VARCHAR(128),
TABLE_CATALOG VARCHAR(128),
TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128),
TABLE_DESCRIPTION VARCHAR(1000),
RecordCount INT,
COLUMN_NAME VARCHAR(128),
ORDINAL_POSITION INT,
DATA_TYPE VARCHAR(128),
COLUMN_DESCRIPTION VARCHAR(1000),
COLUMN_DEFAULT VARCHAR(255),
IS_NULLABLE VARCHAR(3),
FieldPrecision INT,
CHARACTER_SET_NAME VARCHAR(128),
COLLATION_NAME VARCHAR(128),
PK BIT,
FKey_NAME VARCHAR(128),
PKey_NAME VARCHAR(128),
PKEY_TABLE VARCHAR(128),
PKEY_COLUMN VARCHAR(128),
IsIdentity VARCHAR(3),
IsRowGuidCol VARCHAR(3),
IsFullTextIndexed VARCHAR(3),
CHECK_CONSTRAINT VARCHAR(255),
INDEX_CONSTRAINT VARCHAR(255)
)
–Set Primary table/field values
INSERT INTO @Columns
(TABLE_TYPE,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,RecordCount,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,
COLUMN_DEFAULT,IS_NULLABLE,FieldPrecision,CHARACTER_SET_NAME,COLLATION_NAME, FKey_NAME,
IsIdentity, IsRowGuidCol, IsFullTextIndexed)
SELECT T.TABLE_TYPE, T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, max(i.rows),
C.COLUMN_NAME, C.ORDINAL_POSITION,
DATA_TYPE + ISNULL(‘(‘ + CAST([CHARACTER_MAXIMUM_LENGTH] AS VARCHAR(255)) + ‘)’, ”),
COLUMN_DEFAULT, IS_NULLABLE, IsNull(NUMERIC_PRECISION,DATETIME_PRECISION) AS FieldPrecision,
CHARACTER_SET_NAME, COLLATION_NAME, U.CONSTRAINT_NAME,
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsIdentity’),
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsRowGuidCol’),
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsFulltextIndexed’)
FROM sysindexes i, INFORMATION_SCHEMA.Tables T JOIN INFORMATION_SCHEMA.Columns C
ON T.TABLE_SCHEMA = C.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS U
ON T.TABLE_SCHEMA = U.TABLE_SCHEMA
AND T.TABLE_NAME = U.TABLE_NAME
AND C.COLUMN_NAME = U.COLUMN_NAME
AND U.CONSTRAINT_NAME LIKE ‘FK_%’
WHERE (T.TABLE_NAME <> ‘dtproperties’) AND (T.TABLE_NAME NOT LIKE ’sys%’)
AND (TABLE_TYPE = ‘BASE TABLE’) AND (T.TABLE_NAME = object_name(i.id))
GROUP BY T.TABLE_TYPE, T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME,
C.COLUMN_NAME, C.ORDINAL_POSITION,
DATA_TYPE + ISNULL(‘(‘ + CAST([CHARACTER_MAXIMUM_LENGTH] AS VARCHAR(255)) + ‘)’, ”),
COLUMN_DEFAULT, IS_NULLABLE, IsNull(NUMERIC_PRECISION,DATETIME_PRECISION),
CHARACTER_SET_NAME, COLLATION_NAME, U.CONSTRAINT_NAME,
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsIdentity’),
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsRowGuidCol’),
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsFulltextIndexed’)
ORDER BY T.TABLE_TYPE, T.TABLE_SCHEMA, T.TABLE_NAME, C.ORDINAL_POSITION
–Set CHECK_CONSTRAINT values
UPDATE @Columns
SET CHECK_CONSTRAINT = CC.CHECK_CLAUSE
FROM @Columns AS C
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS U
ON C.TABLE_NAME = U.TABLE_NAME
AND C.COLUMN_NAME = U.COLUMN_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS CC
ON U.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
–Set INDEX_CONSTRAINT values
UPDATE @Columns
SET INDEX_CONSTRAINT = U.CONSTRAINT_NAME
FROM @Columns AS C
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS U
ON U.TABLE_NAME = C.TABLE_NAME
AND U.COLUMN_NAME = C.COLUMN_NAME
AND U.CONSTRAINT_NAME LIKE ‘IX_%’
–Set PK values
UPDATE @Columns
SET PK = -1
FROM @Columns AS C
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
ON C.TABLE_NAME = K.TABLE_NAME
AND C.COLUMN_NAME = K.COLUMN_NAME
AND K.CONSTRAINT_NAME LIKE ‘PK_%’
–Set PKey_Name values
UPDATE @Columns
SET PKey_Name = RC.UNIQUE_CONSTRAINT_NAME
FROM @Columns AS C
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
ON RC.CONSTRAINT_NAME = C.FKey_NAME
–Set PKEY_TABLE values
UPDATE @Columns
SET PKEY_TABLE = U.[TABLE_NAME],
PKEY_COLUMN = U.[COLUMN_NAME]
FROM @Columns AS C
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS U
ON U.CONSTRAINT_NAME = C.PKEY_NAME
–Set TABLE_DESCRIPTION and COLUMN_DESCRIPTION values
DECLARE @Table VARCHAR(128)
DECLARE @Field VARCHAR(128)
DECLARE Tables CURSOR FOR
SELECT DISTINCT TABLE_NAME
FROM @Columns
WHERE TABLE_TYPE = ‘BASE TABLE’
OPEN Tables
FETCH NEXT FROM Tables INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
–Set TABLE_DESCRIPTION values
UPDATE @Columns
SET TABLE_DESCRIPTION = CAST(TD.[Value] AS VARCHAR(1000))
FROM ::fn_listextendedproperty(N’MS_Description’, N’user’, ‘dbo’, ‘TABLE’, default, default, default) AS TD
WHERE TD.ObjName = @Table
AND TABLE_NAME = @Table
IF (SELECT COUNT(*) FROM ::fn_listextendedproperty(‘MS_Description’, ‘user’, ‘dbo’, N’TABLE’, @Table, ‘COLUMN’, default)) > 0
BEGIN
DECLARE Fields CURSOR FOR
SELECT DISTINCT COLUMN_NAME
FROM @Columns
WHERE TABLE_TYPE = ‘BASE TABLE’
AND TABLE_NAME = @Table
OPEN Fields
FETCH NEXT FROM Fields INTO @Field
WHILE @@FETCH_STATUS = 0
BEGIN
–Set COLUMN_DESCRIPTION values
UPDATE @Columns
SET COLUMN_DESCRIPTION = CAST(CD.[Value] AS VARCHAR(1000))
FROM ::fn_listextendedproperty(‘MS_Description’, ‘user’, ‘dbo’, N’TABLE’, @Table, ‘COLUMN’, default) as CD
WHERE CD.ObjName = @Field
AND TABLE_NAME = @Table
AND COLUMN_NAME = @Field
FETCH NEXT FROM Fields INTO @Field
END
CLOSE Fields
DEALLOCATE Fields
END
FETCH NEXT FROM Tables INTO @Table
END
CLOSE Tables
DEALLOCATE Tables
SELECT *
FROM @Columns

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 change was, who did it, and when.

You may want to keep this table stored in a separate DB, perhaps adding a column for which DB had the change occur. The WITH ENCRYPTION allows you to encrypt the trigger, so that prying eyes can not see what it is doing and either edit the trigger or find a way around it.

The code:
CREATE TABLE AuditDDLOperations
(
OpID int NOT NULL identity
CONSTRAINT AuditDDLOperationsPK
PRIMARY KEY CLUSTERED,
LoginName sysname NOT NULL,
UserName sysname NOT NULL,
PostTime datetime NOT NULL,
EventType nvarchar(100) NOT NULL,
DDLOp nvarchar(2000) NOT NULL
)
GO

CREATE TRIGGER AuditAllDDL
ON DATABASE
–WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT AuditDDLOperations
(LoginName, UserName, PostTime, EventType, DDLOp)
VALUES (SYSTEM_USER, CURRENT_USER, GETDATE()
, @data.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘nvarchar(100)’)
, @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’) )
RETURN
GO

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 Trigger that you can use to track all DDL changes, but this can become a very manual process to compare the DDL changes across multiple environments and then sync those differences up.

However, a tool from Red-Gate Software called SQL Compare can help solve this issue.

From the Red-Gate website:
Compares and synchronizes SQL Server database schemas
Version 5 (out now) fully engineered for SQL Server 2005

• Automate database comparisons
• Synchronize your databases
• Save hours of tedious work and eliminate manual scripting errors
• All new database objects in SQL Server 2005 supported

Some of the procedures SQL Compare will automate for you:

• Comparing all or just selected database objects
• Generating synchronization scripts
• Taking database snapshots and comparing them, useful for audit trails
• Change histories, ideal for database versioning
• Executing “right-first-time” database synchronizations

With a free 14 day trial, it’s worth it just to take for a “test drive” to see if this is the right tool for your DB needs…

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 depth description of what it does (and how it does it) later when I have time, and am redirected towards this type of task again…

I have not used this code in some time, but as I recall, it needs to be run FROM one of the databases that you are looking for the differences across, since it uses the INFORMATION_SCHEMA views from the current database to determine the tables and fields.

The code:
/*
DECLARE @return_value int, @SQLStatements nvarchar(4000)
EXEC @return_value = [dbo].[LookupTableDiff] @SQLStatements = @SQLStatements OUTPUT, @TableNameToProcess = N’Question’
SELECT @SQLStatements as N’@SQLStatements’
*/
ALTER PROCEDURE [dbo].[LookupTableDiff]
@SQLStatements NVARCHAR(4000) = ” OUTPUT,
@TableNameToProcess nvarchar(4000)
AS
BEGIN
DECLARE @sql NVARCHAR(4000)
DECLARE @sqlField NVARCHAR(4000)
DECLARE @Nullable NVARCHAR(3)
DECLARE @Default NVARCHAR(4000)
DECLARE @DataType NVARCHAR(100)
DECLARE @Server1Name NVARCHAR(4000)
DECLARE @DB1Name NVARCHAR(4000)
DECLARE @Server2Name NVARCHAR(4000)
DECLARE @DB2Name NVARCHAR(4000)
DECLARE @TableName NVARCHAR(4000)
DECLARE @FieldName NVARCHAR(4000)
DECLARE @FirstFieldName NVARCHAR(4000)
DECLARE @Count INT
DECLARE @SERVERS TABLE (
ServerName NVARCHAR (20) NOT NULL ,
DBName NVARCHAR (50) NOT NULL)
INSERT INTO @SERVERS SELECT ‘CAODev’,’[ServerName].CAODev’
INSERT INTO @SERVERS SELECT ‘CAOTest’,’[ServerName].CAOTest’
DECLARE @TABLESTORUN TABLE (
TableName NVARCHAR (100) NOT NULL)
INSERT INTO @TABLESTORUN (TableName) SELECT @TableNameToProcess
CREATE TABLE #TABLES (
TableName NVARCHAR (100) NOT NULL ,
ServerWith NVARCHAR (100) NULL ,
ServerWithOut NVARCHAR (100) NULL ,
ExtraCount INT NULL)
SET @SQLStatements = ”
DECLARE SERVERRUN CURSOR FOR
SELECT SERVER1.ServerName AS Server1Name, SERVER1.DBName AS DB1Name, SERVER2.ServerName AS Server2Name, SERVER2.DBName AS DB2Name
FROM @SERVERS SERVER1, @SERVERS SERVER2
WHERE SERVER1.ServerName<>SERVER2.ServerName AND SERVER1.DBName<>SERVER2.DBName
OPEN SERVERRUN
FETCH NEXT FROM SERVERRUN INTO @Server1Name, @DB1Name, @Server2Name, @DB2Name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE TABLERUN CURSOR FOR
SELECT TableName FROM @TABLESTORUN
OPEN TABLERUN
FETCH NEXT FROM TABLERUN INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘INSERT INTO #TABLES
SELECT ”’ + @TableName + ”’, ”’ + @Server1Name + ”’, ”’ + @Server2Name + ”’, count(*)
FROM ‘ + @DB1Name + ‘.dbo.’ + @TableName + ‘ ‘ + @Server1Name + ‘
LEFT JOIN ‘ + @DB2Name + ‘.dbo.’ + @TableName + ‘ ‘ + @Server2Name + ‘ ON ‘
SET @sqlField = ”
SET @FirstFieldName = ”
DECLARE FIELD CURSOR FOR
SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @TableName)
OPEN FIELD
FETCH NEXT FROM FIELD INTO @FieldName, @Nullable, @Default, @DataType
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT (@FieldName = ‘createdDate’) AND NOT(@FieldName = ‘updatedDate’) AND NOT(@FieldName = ‘updatedBy’)
BEGIN
IF @sqlField = ”
BEGIN
SET @sqlField = ”
SET @FirstFieldName = @FieldName
END
ELSE
BEGIN
SET @sqlField = @sqlField + ‘ AND ‘
END
IF (@Nullable = ‘Yes’) AND (@Default IS NULL) AND (@DataType like ‘%char’)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘ + ” ”,””) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘ + ” ”,””)) ‘
ELSE IF (@Nullable = ‘Yes’) AND (@Default IS NULL) AND (@DataType = ‘datetime’)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘ ,”1/1/1900”) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘,”1/1/1900”)) ‘
ELSE IF (@Nullable = ‘Yes’) AND (@Default IS NULL)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘,”0”) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘,”0”)) ‘
ELSE
SET @sqlField = @sqlField + ‘(‘ + @Server1Name + ‘.’ + @FieldName + ‘ = ‘ + @Server2Name + ‘.’ + @FieldName + ‘) ‘
END
FETCH NEXT FROM FIELD INTO @FieldName, @Nullable, @Default, @DataType
END
CLOSE FIELD
DEALLOCATE FIELD
SET @sql = @sql + @sqlField + ‘WHERE (((‘ + @Server2Name + ‘.’ + @FirstFieldName + ‘) Is Null)) ‘
EXEC sp_executesql @sql
FETCH NEXT FROM TABLERUN INTO @TableName
END
CLOSE TABLERUN
DEALLOCATE TABLERUN
FETCH NEXT FROM SERVERRUN INTO @Server1Name, @DB1Name, @Server2Name, @DB2Name
END
CLOSE SERVERRUN
DEALLOCATE SERVERRUN
DECLARE TABLERUN CURSOR FOR
SELECT TableName, ServerWith, ServerWithOut FROM #TABLES WHERE ExtraCount >0 ORDER BY TableName, ServerWith, ServerWithOut
OPEN TABLERUN
FETCH NEXT FROM TABLERUN INTO @TableName, @Server1Name, @Server2Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DB1Name = DBName FROM @SERVERS WHERE ServerName = @Server1Name
SELECT @DB2Name = DBName FROM @SERVERS WHERE ServerName = @Server2Name
SET @sql = ‘SELECT ”’ + @TableName + ”’, ”’ + @Server1Name + ”’, ”’ + @Server2Name + ”’, *
FROM ‘ + @DB1Name + ‘.dbo.’ + @TableName + ‘ ‘ + @Server1Name + ‘
LEFT JOIN ‘ + @DB2Name + ‘.dbo.’ + @TableName + ‘ ‘ + @Server2Name + ‘ ON ‘
SET @sqlField = ”
SET @FirstFieldName = ”
DECLARE FIELD CURSOR FOR
SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @TableName)
OPEN FIELD
FETCH NEXT FROM FIELD INTO @FieldName, @Nullable, @Default, @DataType
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT (@FieldName = ‘createdDate’) AND NOT(@FieldName = ‘updatedDate’) AND NOT(@FieldName = ‘updatedBy’)
BEGIN
IF @sqlField = ”
BEGIN
SET @sqlField = ”
SET @FirstFieldName = @FieldName
END
ELSE
BEGIN
SET @sqlField = @sqlField + ‘ AND ‘
END
IF (@Nullable = ‘Yes’) AND (@Default IS NULL) AND (@DataType like ‘%char’)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘ + ” ”,””) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘ + ” ”,””)) ‘
ELSE IF (@Nullable = ‘Yes’) AND (@Default IS NULL) AND (@DataType like ‘datetime’)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘,”1/1/1900”) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘,”1/1/1900”)) ‘
ELSE IF (@Nullable = ‘Yes’) AND (@Default IS NULL)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘,”0”) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘,”0”)) ‘
ELSE
SET @sqlField = @sqlField + ‘(‘ + @Server1Name + ‘.’ + @FieldName + ‘ = ‘ + @Server2Name + ‘.’ + @FieldName + ‘) ‘
END
FETCH NEXT FROM FIELD INTO @FieldName, @Nullable, @Default, @DataType
END
CLOSE FIELD
DEALLOCATE FIELD
SET @sql = @sql + @sqlField + ‘WHERE (((‘ + @Server2Name + ‘.’ + @FirstFieldName + ‘) Is Null)) ‘
PRINT @sql
IF @SQLStatements = ”
SET @SQLStatements = @sql
ELSE
SET @SQLStatements = @SQLStatements + ‘<br><br>’ + @sql
FETCH NEXT FROM TABLERUN INTO @TableName, @Server1Name, @Server2Name
END
CLOSE TABLERUN
DEALLOCATE TABLERUN
SELECT *
FROM #TABLES
WHERE ExtraCount >0
ORDER BY TableName, ServerWith, ServerWithOut
DROP TABLE #TABLES
END

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 record count from all tables in a DB.

With large datasets and a large number of tables, running SELECT Count(*) on each of the tables becomes time consuming and tedious, but by using the system tables you can quickly and easily get the record count of all tables in your DB.

The code:
SELECT ‘Owner’=convert(char(10),t.TABLE_SCHEMA),
‘Table Name’=convert(char(25),t.TABLE_NAME),
‘Record Count’=max(i.rows)
FROM sysindexes i, INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME = object_name(i.id)
AND t.TABLE_TYPE = ‘BASE TABLE’
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME

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 here, but when developing large scale applications, using the Patterns and Practices for the Data and Application, can save a tremendous amount of time, and result in a more stable, and better performing system in the end.

If you have any questions, please feel free to post, and hopefully I can either help you from my own development experience or point you in the right direction.

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 INDEXDEGRAG
DBCC REINDEX

The following script takes advantage of these to reindex tables in the current DB.

Much of this code is rewritten from a few similar T-SQL statements found on other sites (I will add links to them later when I find them again) with a few modifications/additions of my own.

The one update I would make (and will make the next time I work with this code) would be to convert this to a Stored Procedure where the parameters are passed in instead of hard-coded.

The following lines (which are found in the T-SQL below) are the only lines you would want to modify for your own scenario:

SELECT @maxfrag = 10.0
    (Allowing more fragmentation is generally better for massive updates, less fragmentation is better for more reads. You’ll want to test to find the fragmentation level that works best for your own environment)
SET @Server = ‘TESTSERVER’
    (I use this for reporting purposes. Generally I am defragmenting many DBs and posting the final results into Excel. you could just as easily post the results to a table to see how quickly your indexes fragment over time)
SET @MaxCount = 10000000
    (In a production environment, I generally find that defragmenting a table of more than 10mil records takes too long and causes too much blocking, so I save those for off peak hours)
SET @MinCount = 10000
    (Fragmentation on tables of less than 10,000 records in my experience has little to no impact on performance, so I generally ignore those tables)

SELECT @execstr = ‘DBCC INDEXDEFRAG (0, ‘ + RTRIM(@tablename) + ‘,’ + RTRIM(@IndexName) + ‘)’
    (In this T-SQL this line is commented out. Using DBCC INDEXDEFRAG does a better job but causes VERY heavy transactions, which if left unchecked, can quite easily grow out of control.)
SELECT @execstr = ‘DBCC DBREINDEX (‘ + RTRIM(@tablename) + ‘,’ + RTRIM(@IndexName) + ‘, ‘ + CONVERT(varchar(15),100-@maxfrag) + ‘)’
    (In this T-SQL this line is used. Using DBCC DBReindex does not do as good a job but does not create massive transactions, which is better in many live production environments.)

The code:

SET NOCOUNT ON
DECLARE @Owner VARCHAR (128)
DECLARE @tablename VARCHAR (128)
DECLARE @IndexName VARCHAR (255)
DECLARE @RecordCount INT
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
DECLARE @Server VARCHAR(10)
DECLARE @MaxCount INT
DECLARE @MinCount INT
– Decide on the maximum fragmentation to allow
SELECT @maxfrag = 10.0
SET @Server = ‘TESTSERVER’
SET @MaxCount = 10000000
SET @MinCount = 10000
– Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
– Declare and open cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
OPEN tables
– Loop through all the tables in the database
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
– Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)
FETCH NEXT FROM tables INTO @tablename
END
CLOSE tables
DEALLOCATE tables
– Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM #fraglist
WHERE LogicalFrag >= (@maxfrag + 5)
AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0
– Open the cursor
OPEN indexes
– loop through the indexes
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag, @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @RecordCount=max(i.rows)
FROM sysindexes i
WHERE object_name(i.id)=@tablename
If (@RecordCount BETWEEN @MinCount and @MaxCount)
BEGIN
– SELECT @execstr = ‘DBCC INDEXDEFRAG (0, ‘ + RTRIM(@tablename) + ‘,’ + RTRIM(@IndexName) + ‘)’
SELECT @execstr = ‘DBCC DBREINDEX (‘ + RTRIM(@tablename) + ‘,’ + RTRIM(@IndexName) + ‘, ‘ + CONVERT(varchar(15),100-@maxfrag) + ‘)’
PRINT @execstr + ‘: – fragmentation currently ‘ + RTRIM(CONVERT(varchar(15),@frag)) + ‘%’
EXEC (@execstr)
END
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag, @IndexName
END
CLOSE indexes
DEALLOCATE indexes
– Delete the temporary table
SELECT ‘RecordCount’ = max(i.rows), ‘Server’=@Server, t.ObjectName, ‘Frag’=avg(t.LogicalFrag), RTRIM(t.IndexName)
FROM sysindexes i, #fraglist t
WHERE(t.ObjectName = object_name(i.id)) AND (t.LogicalFrag >= @maxfrag)
GROUP BY t.ObjectName, t.IndexName
HAVING (max(i.rows)>@MinCount)
ORDER BY t.ObjectName
DROP TABLE #fraglist
GO

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 I had written a Visual Basic (3.0?)  routine to encrypt data back in the mid 90’s. At some point on another project I had a reason (although I don’t recall why any more) o move that code to the back-end, and thus wrote this SQL script.

This routine takes 3 parameters (the string to manipulate; ‘Encrypt’ or ‘Decrypt’; and the encryption key value), and doing some quite simple math, hides the original value from prying eyes. Since the SP is created using the WITH ENCRYPTION parameter, nobody can see the actual math you are doing to the value to determine the return value. With a few minor modifications you could include a SALT value, as another parameter or keep the encryption key stored in the code itself.

While the recommended approach to encryption would be to choose more advanced option using the dotNET Framework, this should give at least a cursory overview of the potential for string manipulation to secure data.

The code:
CREATE procedure SimpleEncryption
(@strToConvert varchar(2000),
@strConvertType char(1),
@strEncryptKey varchar(100))
WITH ENCRYPTION AS
DECLARE @strConvertTo varchar(2000)
DECLARE @strConLetter char(5)
DECLARE @strKeyLetter char(5)
DECLARE @i int
DECLARE @k int
set @i = 0
set @strConvertTo = ”
if @strEncryptKey <> ”
IF @strConvertType = ‘E’
WHILE @i < len(@strToConvert)
BEGIN
set @i = @i + 1
set @strConLetter = ASCII(SUBSTRING(@strToConvert, @i, 1))
set @k = @i % len(@strEncryptKey)
if @k = 0
set @k = len(@strEncryptKey)
set @strKeyLetter = ASCII(SUBSTRING(@strEncryptKey, @k, 1))
set @strConvertTo = @strConvertTo + char(ASCII(SUBSTRING(@strToConvert, @i, 1)) + ASCII(SUBSTRING(@strEncryptKey, @k, 1)))
END
ELSE IF @strConvertType=’D’
WHILE @i < len(@strToConvert)
BEGIN
set @i = @i + 1
set @strConLetter = ASCII(SUBSTRING(@strToConvert, @i, 1))
set @k = @i % len(@strEncryptKey)
if @k = 0
set @k = len(@strEncryptKey)
set @strKeyLetter = ASCII(SUBSTRING(@strEncryptKey, @k, 1))
set @strConvertTo = @strConvertTo + char(ASCII(SUBSTRING(@strToConvert, @i, 1)) – ASCII(SUBSTRING(@strEncryptKey, @k, 1)))
END
ELSE
select ‘No Valid Parameter Passed’
else
select ‘No Valid Encyption Key Passed’
select @strConvertTo
GO

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 takes it to a new (and potentially dangerous) level.

In essence, what I am doing here is creating a table variable (then cursor) of all the old/new text values. While looping through this data, I scan for all fields in all tables that are of type %CHAR% and create dynamic SQL to determine if each field contains the specific text I’m looking to replace. If it does, then I create more dynamic SQL to replace the old text with the new text.
Be aware of exactly what this is doing, because the potential exists to literally ruin your data. For example, if you replace “t” with “th” then “HTML” becomes “HTHML”, etc..

The code:
DECLARE @TextUpdate TABLE (
OldText NVARCHAR (255) NOT NULL ,
NewText NVARCHAR (255) NOT NULL)
INSERT INTO @TextUpdate SELECT ‘first old value’,'new value #1′
INSERT INTO @TextUpdate SELECT ‘2nd old value’,'new value 2′
DECLARE @OldText NVARCHAR (255)
DECLARE @NewText NVARCHAR (255)
DECLARE @TABLE_SCHEMA NVARCHAR(255)
DECLARE @TABLE_NAME NVARCHAR(255)
DECLARE @COLUMN_NAME NVARCHAR(255)
DECLARE @SQL NVARCHAR(2000)
DECLARE TextUpdate CURSOR FOR
SELECT * FROM @TextUpdate
OPEN TextUpdate
FETCH NEXT FROM TextUpdate INTO @OldText, @NewText
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE TABLES CURSOR FOR
SELECT T.TABLE_SCHEMA, T.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.Tables T
JOIN INFORMATION_SCHEMA.Columns C ON T.TABLE_SCHEMA = C.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAME
WHERE TABLE_TYPE = ‘BASE TABLE’ AND (DATA_TYPE LIKE ‘%CHAR%’)– OR DATA_TYPE LIKE ‘%TEXT%’)
ORDER BY T.TABLE_TYPE, T.TABLE_SCHEMA, T.TABLE_NAME, C.ORDINAL_POSITION
OPEN TABLES
FETCH NEXT FROM TABLES INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = ‘IF EXISTS(SELECT * FROM [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + '] WHERE [' + @COLUMN_NAME + '] LIKE ”%’ + @OldText + ‘%”)
BEGIN
UPDATE [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + ']
SET [' + @COLUMN_NAME + '] = LEFT([' + @COLUMN_NAME + '], CHARINDEX(”’ + @OldText + ”’, [' + @COLUMN_NAME + '])-1) + ”’ +
@NewText + ”’ + SUBSTRING([' + @COLUMN_NAME + '],CHARINDEX(”’ + @OldText + ”’, [' + @COLUMN_NAME + '])+ ‘ + CAST(Len(@OldText) AS VARCHAR(100)) + ‘,Len([' + @COLUMN_NAME + ']))
WHERE [' + @COLUMN_NAME + '] LIKE ”%’ + @OldText + ‘%”
END’
EXEC sp_executesql @SQL
FETCH NEXT FROM TABLES INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
END
CLOSE TABLES
DEALLOCATE TABLES
FETCH NEXT FROM TextUpdate INTO @OldText, @NewText
END
CLOSE TextUpdate
DEALLOCATE TextUpdate