House Rules

If there’s a beef, it’s your fault. If you break it, you pay for it, plus sales tax and installation. If you attempt to remove any person or persons from these premises involuntarily, by force or coercion as defined by the house, you will be surrendered to the police in a damaged condition. The decisions of your bartender are final, and the management doesn’t want to know you. The first one’s on the house; have a good time.

Callahan’s Crosstime Saloon, Spider Robinson

One of my favorite books.

Clear Database Tuning Advisor Tables

When using the SQL Server Database Tuning Advisor (DTA) the UI can get clustered with all the completed sessions. You could right-click each session and delete them. Or, you could just run this SQL:

USE msdb
GO

-- -------------------------------------------------------------------------------
-- Clear Database Tuning Advisor Tables
-- -------------------------------------------------------------------------------

TRUNCATE TABLE DTA_output;
TRUNCATE TABLE DTA_progress;
TRUNCATE TABLE DTA_reports_indexcolumn;
TRUNCATE TABLE DTA_reports_partitionscheme;
TRUNCATE TABLE DTA_reports_querycolumn;
TRUNCATE TABLE DTA_reports_querydatabase;
TRUNCATE TABLE DTA_reports_queryindex;
TRUNCATE TABLE DTA_reports_querytable;
TRUNCATE TABLE DTA_reports_tableview;
TRUNCATE TABLE DTA_tuninglog;
GO

DELETE FROM DTA_reports_table;
DELETE FROM DTA_reports_partitionfunction;
DELETE FROM DTA_reports_database;
DELETE FROM DTA_reports_column;
DELETE FROM DTA_reports_index;
DELETE FROM DTA_input;
DELETE FROM DTA_reports_query;
GO

Wipe all objects in a database

Have you ever wanted to wipe out a whole database? All the objects?

Tested on SQL Server 2008 R2:

IF ( EXISTS ( SELECT * FROM sys.procedures p WHERE p.object_id = OBJECT_ID('[dbo].[ResetWarehouse]') ) )
DROP PROCEDURE [dbo].[ResetWarehouse];
GO

CREATE PROCEDURE [dbo].[ResetWarehouse] (
  @booDebug BIT = 0
)
AS
SET NOCOUNT ON;
DECLARE @svr SYSNAME = @@SERVERNAME;
IF ( @svr != 'YOUR_DEV_SERVER' )
BEGIN
  RAISERROR('WRONG SERVER: %s IS NOT YOUR_DEV_SERVER',25,1,@svr) WITH LOG;
  RETURN -1;
END

-- =============================================
-- Author: Brad Simpson
-- Create date: 2008-08-25@10:24
-- Description:	clean up
-- =============================================

SET @booDebug = COALESCE(@booDebug,0);
DECLARE @SQL as NVARCHAR(max);
DECLARE @tblObjects TABLE (
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
  ,ObjectId INT NOT NULL
  ,SchemaName SYSNAME NOT NULL
  ,ObjectName SYSNAME NOT NULL
  ,ObjectType SYSNAME NOT NULL
  ,ObjectIdParent INT NULL
  ,SchemaParent SYSNAME NULL
  ,ObjectParent SYSNAME NULL
  ,ObjectTypeParent SYSNAME NULL
  ,CountLoops INT NOT NULL DEFAULT(0)
);

WITH typeDesc ( OrderBy,type_desc ) AS (
SELECT 01,'FOREIGN_KEY_CONSTRAINT'
UNION ALL SELECT 02,'UNIQUE_CONSTRAINT'
UNION ALL SELECT 03,'DEFAULT_CONSTRAINT'
UNION ALL SELECT 04,'PRIMARY_KEY_CONSTRAINT'
UNION ALL SELECT 05,'SQL_INLINE_TABLE_VALUED_FUNCTION'
UNION ALL SELECT 06,'SQL_SCALAR_FUNCTION'
UNION ALL SELECT 07,'SQL_TABLE_VALUED_FUNCTION'
UNION ALL SELECT 08,'SQL_STORED_PROCEDURE'
UNION ALL SELECT 09,'USER_TABLE'
UNION ALL SELECT 10,'VIEW'
)
INSERT INTO @tblObjects ( [ObjectId],[SchemaName],[ObjectName],[ObjectType],[ObjectIdParent],[SchemaParent],[ObjectParent],[ObjectTypeParent] )
SELECT
   ObjectId = o.object_id
  ,SchemaName = s.name
  ,ObjectName = o.name
  ,ObjectType = o.type_desc
  ,ObjectIdParent = po.object_id
  ,SchemaParent = ps.name
  ,ObjectParent = po.name
  ,ObjectTypeParent = po.type_desc
FROM
  sys.schemas s
  INNER JOIN sys.objects o ON s.schema_id = o.schema_id
  LEFT OUTER JOIN sys.objects po ON o.parent_object_id != 0 AND o.parent_object_id = po.object_id
  LEFT OUTER JOIN sys.schemas ps ON po.schema_id = ps.schema_id
  INNER JOIN typeDesc td ON o.type_desc = td.type_desc
WHERE
  s.name NOT IN ('dbo','sys','INFORMATION_SCHEMA')
  AND NOT ( s.name = 'dbo' AND o.name = 'ResetWarehouse' AND o.type_desc = 'SQL_STORED_PROCEDURE' )
ORDER BY td.OrderBy,o.type_desc,s.name,o.name;

DECLARE @ID INT;
DECLARE @ObjectId INT;
DECLARE @SchemaName SYSNAME;
DECLARE @ObjectName SYSNAME;
DECLARE @ObjectType SYSNAME;
DECLARE @ObjectIdParent INT;
DECLARE @SchemaParent SYSNAME;
DECLARE @ObjectParent SYSNAME;
DECLARE @ObjectTypeParent SYSNAME;
WHILE ( EXISTS ( SELECT * FROM @tblObjects ) )
BEGIN
  SELECT TOP(1)
     @ID = o.ID
    ,@SQL = ''
    ,@ObjectId = o.ObjectId
    ,@SchemaName = o.SchemaName
    ,@ObjectName = o.ObjectName
    ,@ObjectType = o.ObjectType
    ,@ObjectIdParent = o.ObjectIdParent
    ,@SchemaParent = o.SchemaParent
    ,@ObjectParent = o.ObjectParent
    ,@ObjectTypeParent = o.ObjectTypeParent
  FROM @tblObjects o
  ORDER BY o.CountLoops,o.ID;

  --IF ( @ObjectIdParent IS NULL )
  --  RAISERROR('Object: %s.%s: %s',0,1,@SchemaName,@ObjectName,@ObjectType) WITH NOWAIT;
  --ELSE
  --  RAISERROR('Object: %s.%s: %s ON %s.%s: %s',0,1,@SchemaName,@ObjectName,@ObjectType,@SchemaParent,@ObjectParent,@ObjectTypeParent) WITH NOWAIT;

  SELECT @SQL = CASE @ObjectType
    WHEN 'DEFAULT_CONSTRAINT'     THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'
    WHEN 'FOREIGN_KEY_CONSTRAINT' THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'
    WHEN 'PRIMARY_KEY_CONSTRAINT' THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'
    WHEN 'UNIQUE_CONSTRAINT'      THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'

    WHEN 'SQL_INLINE_TABLE_VALUED_FUNCTION' THEN 'DROP FUNCTION ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'SQL_SCALAR_FUNCTION'              THEN 'DROP FUNCTION ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'SQL_TABLE_VALUED_FUNCTION'        THEN 'DROP FUNCTION ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'

    WHEN 'SQL_STORED_PROCEDURE' THEN 'DROP PROCEDURE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'VIEW'                 THEN 'DROP VIEW '      + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'USER_TABLE'           THEN 'DROP TABLE '     + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    ELSE NULL END

  UPDATE o
  SET o.CountLoops = COALESCE(o.CountLoops,1) + 1
  FROM @tblObjects o
  WHERE o.ID = @ID;

  SET @SQL = NULLIF(LTRIM(RTRIM(@SQL)),'');
  IF ( @SQL IS NOT NULL )
  BEGIN
    BEGIN TRY
      --RAISERROR('%s',0,1,@SQL) WITH NOWAIT;
      IF ( @booDebug = 0 ) EXEC sp_executesql @SQL;
      DELETE o FROM @tblObjects o WHERE o.ID = @ID;
    END TRY
    BEGIN CATCH
      SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;
      RETURN 0;
    END CATCH
  END  
  DELETE FROM o FROM @tblObjects o WHERE o.CountLoops > 100;
END
GO