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.'