ENABLE/DISABLE TRIGGER must follow a semicolon

Can't get an ENABLE or DISABLE TRIGGER to even parse, much less execute? This might be the cause,

Due to a Microsoft glitch, ENABLE and DISABLE are not yet keywords. This means for them to be recognized as the beginning of a statement, the preceeding statement must end with a semi-colon. But putting it on the front of the offending line is clearer, to me:

;DISABLE TRIGGER dbo.trDoSomething ON MyTable

For more info, see: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=307937&wa=wsignin1.0

Using PIVOT - An (honest!) Simple Example

The concept of the PIVOT keyword is pretty simple: rotate the results so that the rows and columns are transposed. But the syntax is, well, kinda weird. This example uses PIVOT in the simplest way, to make it easier to pick through the code and see what's going on.

First let's create the example table, which contains one row for each girl. In our admittedly skewed sample set, all girls have cats, and there are only five girl names represented: Ada, Bea, Cat, Dot, and Eve. (If anyone knows of a three-letter girl's name beginning with "C", I'd be obliged.)

-- Drop and re-create the table. 
IF OBJECT_ID('dbo.Girl') IS NOT NULL DROP TABLE dbo.Girl

CREATE TABLE dbo.Girl
(
    GirlID INT IDENTITY(1,1), 
    Name       NVARCHAR(50),
    CatCount   TINYINT              
)

Now let's add some reasonable (except for one "crazy cat lady", apparently) data to the table.

-- Populate the table.  
INSERT INTO dbo.Girl
    (Name, CatCount)
VALUES
    ('DOT', 9), ('CAT', 3), ('CAT', 1), ('ANN', 3),
    ('ANN', 3), ('BEA', 3), ('EVE', 4), ('EVE', 3),
    ('EVE', 3), ('EVE', 4), ('EVE', 4), ('ANN', 3),
    ('EVE', 3), ('BEA', 5), ('EVE', 3), ('ANN', 1),
    ('EVE', 1), ('EVE', 3), ('EVE', 3), ('CAT', 103),
    ('BEA', 2), ('EVE', 3), ('CAT', 3), ('DOT', 2),
    ('DOT', 4), ('EVE', 3), ('DOT', 9), ('BEA', 2)

One of the common things we do with data like this is to create subtotals by value. In this case, we can find the total number of cats by girl-name.

-- Group by girls' name to see which name has the most cats. 
-- This displays the girls' names as rows.
SELECT Name           AS 'Girl''s Name',
       SUM(CatCount)  AS 'Total Cats'
  FROM dbo.Girl
 GROUP BY Name

The results would look like this:

-- Girl's Name Total Cats
   ANN          10
   BEA          12
   CAT          110
   DOT          24
   EVE          37

But by using the PIVOT keyword, we can also display the data with the girls' names as the columns. If you think about it, that's pretty cool: it turns data into metadata.

-- Get the same data, but with the girls' names as columns. 
SELECT  ANN  AS 'Total Cats: Ann',
        BEA  AS 'Total Cats: Bea',
        CAT  AS 'Total Cats: Cat',
        DOT  AS 'Total Cats: Dot',
        EVE  AS 'Total Cats: Eve'
  FROM
    (SELECT Name,
            CatCount
       FROM dbo.Girl) AS tSource
PIVOT
(
    SUM(CatCount)
    FOR Name IN (ANN, BEA, CAT, DOT, EVE)
) AS tPivoted

The results would look like this:

-- Total Cats: Ann    Total Cats: Bea    Total Cats: Cat    Total Cats: Dot    Total Cats: Eve
   10                 12                 110                24                 37

Of course, this capability comes at a price: you have to explicitly list the five girl names, which means you have to know what the values are in advance. Often, that's not a big problem: for example, the months of the year.

Convert Any Integer Type to a Hex String

Here's a user-defined function to take any integer from a TINYINT to a BIGINT and return it as a hex number in a VARCHAR like '0x01033FA2':

-----------------------------------------------------------------------------
-- Drop any existing function.
-----------------------------------------------------------------------------

IF OBJECT_ID('dbo.Hexadecimal') IS NOT NULL
BEGIN
   RAISERROR('Dropping user-defined function Hexadecimal', 10, 1) WITH NOWAIT, LOG
   DROP FUNCTION dbo.Hexadecimal
END


-----------------------------------------------------------------------------
-- Create function to take any integer input and return hex as a string.
-----------------------------------------------------------------------------

RAISERROR('Creating user-defined function Hexadecimal', 10, 1) WITH NOWAIT, LOG
GO

CREATE FUNCTION dbo.Hexadecimal(@vbInput VARBINARY(255))
RETURNS VARCHAR(18) WITH EXECUTE AS CALLER AS
BEGIN
 DECLARE @sResult   VARCHAR(18)  SET @sResult   = '0x'
 DECLARE @i         INT          SET @i         = 1
 DECLARE @nInputLen INT          SET @nInputLen = DATALENGTH(@vbInput)
 DECLARE @nChar     INT          SET @nChar     = 0
 DECLARE @nHiInt    INT          SET @nHiInt    = 0
 DECLARE @nLoInt    INT          SET @nLoInt    = 0

 WHILE (@i <= @nInputLen)
 BEGIN
  SET @nChar  = CONVERT(INT, SUBSTRING(@vbInput, @i, 1))
  SET @nHiInt = FLOOR(@nChar / 16)
  SET @nLoInt = @nChar - (@nHiInt * 16)

  SET @sResult = @sResult +
       SUBSTRING('0123456789ABCDEF', @nHiInt + 1, 1) +
       SUBSTRING('0123456789ABCDEF', @nLoInt + 1, 1)
  SET @i = @i + 1
 END

 RETURN @sResult
END

GO

RAISERROR('Created  user-defined function Hexadecimal', 10, 1) WITH NOWAIT, LOG


/* Testing.
declare @return char(18)  set @return = ''
declare @bit    bit       set @bit    = 1
declare @tiny   tinyint   set @tiny   = 255
declare @small  smallint  set @small  = 32767
declare @int    int       set @int    = 2147483647
declare @big    bigint    set @big    = 9223372036854775807

print '@bit   maximum is: ' + dbo.Hexadecimal(@bit)
print '@tiny  maximum is: ' + dbo.Hexadecimal(@tiny)
print '@small maximum is: ' + dbo.Hexadecimal(@small)
print '@int   maximum is: ' + dbo.Hexadecimal(@int)
print '@big   maximum is: ' + dbo.Hexadecimal(@big)
-- End testing. */

Deleting User Objects in Master Database

This script erases everything in the master database that is not Microsoft-shipped. Be careful: some user objects belong in the master database (database triggers, security objects, for example). Backing up the master database first would be a prudent step.

-- Drops all user objects that have been accidentally created in the master database.
USE master

DECLARE @sName     sysname
DECLARE @sTypeDesc NVARCHAR(1024)
DECLARE @sType     NVARCHAR(1024)
DECLARE @nScore    INT
DECLARE @sSql      NVARCHAR(1024)

DECLARE TheCursor CURSOR FOR
 SELECT name, type_desc
   FROM sys.objects
  WHERE is_ms_shipped = 0

OPEN TheCursor

FETCH NEXT FROM TheCursor
 INTO @sName, @sTypeDesc

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @sType  = REVERSE(@sTypeDesc)
 SET @nScore = CHARINDEX('_', @sType)
 SET @sType  = SUBSTRING(@sType, 0, @nScore)
 SET @sType  = REVERSE(@sType)

 SET @sSql = 'DROP ' + @sType + ' ' + @sName
 RAISERROR('%s', 10, 1, @sSql) WITH NOWAIT 
 EXEC sp_sqlexec @sSql
 
 FETCH NEXT FROM TheCursor
  INTO @sName, @sTypeDesc
END

CLOSE TheCursor
DEALLOCATE TheCursor

PRINT 'Done.'