More code...

So you're about to change a column in the enormous database you inherited (or got stuck with,) and you haven't the slightest idea which of the hundreds of stored procedures and triggers you're about to break. Will you create a bug that bankrupts the company and requires you to blame some perfectly innocent coworker to keep from getting fired?

Microsoft SQL Server doesn't have a search function, but this procedure adds one for you. It could be improved to show the offending line(s), but it's still very useful to be able to pinpoint specific procedures, functions, and triggers that contain the search text.

This procedure works on SQL2000 and SQL2005. 

Bonus fact: The 'sp_' prefix tells SQL server to look for a stored procedure in the master database first, and look in your local database second. This of course is why system procedures start with 'sp_'. It is also why you should never use this prefix for your procedures - SQL will go looking in the master database first, which of course is a waste of time.

CREATE PROC sp_search_code
(
@SearchStr varchar(100),
@RowsReturned int = NULL OUT
)
AS

/******************************************************************************

Purpose: To search the stored procedure, UDF, trigger code for a given keyword.
Install this in the master database of the server, then run from the
database you wish to search.
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Added to SQL2000 by Jim Rogers

******************************************************************************/

BEGIN
SET NOCOUNT ON
SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
CASE 
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1 
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 
THEN 'Extended stored procedure' 
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1 
THEN 'Stored Procedure' 
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1 
THEN 'Trigger' 
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 
THEN 'Table-valued function' 
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 
THEN 'Inline function' 
END AS 'Object type',
LEN(c.text) AS Length
INTO #temp
FROM syscomments c
LEFT OUTER JOIN syscomments c1 ON c.id = c1.id AND c1.colid = c.colid + 1
JOIN sysobjects o ON c.id = o.id
WHERE c.text + CASE WHEN c1.text IS NULL THEN '' ELSE c1.text END 
LIKE '%' + @SearchStr + '%' AND
c.encrypted = 0 AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 
)

ORDER BY 'Object type', 'Object name'
SET @RowsReturned = @@ROWCOUNT
END

SELECT * FROM #temp
DROP TABLE #temp

GO

To continue my code dump, here is a little SQL stored procedure that comes in handy when debugging or testing requires constantly restoring a database. Inevitably there are various processes with connections open, and the restore doesn't work until each is found and killed.

This procedure simply loops through the open connections for a given database and kills each one.

CREATE PROCEDURE sp_KillAll
@DbName VARCHAR(100)
AS
/***********************************************************************
Kill all the spid's attached to a particular database. Very useful when 
continually restoring backups for testing.

Create this in the master database, and call it from anywhere; but you
can't kill your own process!
***********************************************************************/

IF db_id(@DbName) = NULL 
BEGIN
    PRINT 'DataBase dose not Exist'
END
ELSE
BEGIN
    DECLARE @spId VARCHAR(30)
        
    DECLARE TmpCursor CURSOR FOR
    SELECT  'Kill ' + CONVERT(VARCHAR, spid)  AS spId
      FROM master..SysProcesses
      WHERE db_Name(dbID) = @DbName
      AND spId <> @@SpId
      AND dbID <> 0
      
    OPEN TmpCursor
        
    FETCH NEXT FROM TmpCursor INTO @spId 
        
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC (@spId)
     
        FETCH NEXT FROM TmpCursor INTO @spId 
    END

    CLOSE TmpCursor
    DEALLOCATE TmpCursor
END 
GO 

I'm reorganizing my website and getting rid of the page with miscellaneous code. But I would hate for that code to go to the great recycle bin in the sky. That's what blogs are for, right?

So here's the first bit of code, a base class called WritableObject, in VB.NET. It uses reflection to write its members out in ToString(). I've found this useful when debugging stuff and wanting to "watch the data go by," as it were.

Imports System.Text
Imports System.Reflection

 Public Class WritableObject

    'This is an object that is capable of listing the names 
    'and values of its fields and properties.  Derive from 
    'this class, but don't override the tostring() function;
    'the ToString() here will output all the public properties 
    'and fields of the derived object.

    Public Overrides Function toString() As String
        Dim mi() As MemberInfo
        Dim item As MemberInfo
        Dim length As Integer
        Dim obj As Object
        Dim b As StringBuilder = New StringBuilder
        b.Capacity = 5000

        Try
            'Write out the type of object I am
            b.Append(Me.GetType.ToString())
            b.Append(vbCrLf)

            'And write out the fields
            mi = Me.GetType.FindMembers( _
                MemberTypes.Field, _
                BindingFlags.Public Or BindingFlags.Instance, _
                Nothing, _
                Nothing)

            'Look for the longest name, so I can line up the items/properties
            length = 0
            For Each item In mi
                If item.Name.Length > length Then length = item.Name.Length
            Next

            For Each item In mi
                b.Append(item.Name)
                b.Append(New String(" ", length - item.Name.Length))
                b.Append(": ")
                If CType(item, FieldInfo).FieldType.Name = "ArrayList" Then
                    Dim list As ArrayList
                    list = Me.GetType.InvokeMember( _
                        item.Name, BindingFlags.GetField, Nothing, Me, Nothing)
                    If Not list Is Nothing Then
                        For Each o As Object In list
                            b.Append(vbCrLf)
                            b.Append(o.ToString())
                        Next
                    End If
                Else
                    'If item.FieldType.fullName Then
                    obj = Me.GetType.InvokeMember( _
                        item.Name, BindingFlags.GetField, Nothing, Me, Nothing)
                    If Not obj Is Nothing Then b.Append(obj.ToString())
                End If
                b.Append(vbCrLf)
            Next
        Catch ex As Exception
            b.Append("Error writing to string: " + ex.Message)
        End Try

        'Return the constructed string
        Return b.ToString()
    End Function
End Class

I needed to convert HSV (Hue, Saturation, Value) colors to RGB to make a hue control for my AS3 project. I found a function to do this and ported it, so here goes:

// Get an RGB value as uint from hue, saturation, value
// Each argument has the range 0 to 1
private function getRBGfromHSV(H:Number, S:Number, V:Number):uint
{
  // Adapted from: http://easyrgb.com/math.php?MATH=M21#text21
  var R:uint, G:uint, B:uint;
  var var_h:Number;
  var var_i:int;
  var var_1:Number, var_2:Number, var_3:Number;
  var var_r:Number, var_g:Number, var_b:Number;
 
  if (S == 0)
  {
     R = V * 255;
     G = V * 255;
     B = V * 255;
  }
  else
  {
     var_h = H * 6;
     if ( var_h >= 6 )
       var_h = 0;         // H must be < 1
     var_i = Math.floor(var_h);   
     var_1 = V * ( 1 - S );
     var_2 = V * ( 1 - S * ( var_h - var_i ) );
     var_3 = V * ( 1 - S * ( 1 - ( var_h - var_i ) ) );

     if      ( var_i == 0 ) { var_r = V     ; var_g = var_3 ; var_b = var_1; }
     else if ( var_i == 1 ) { var_r = var_2 ; var_g = V     ; var_b = var_1; }
     else if ( var_i == 2 ) { var_r = var_1 ; var_g = V     ; var_b = var_3; }
     else if ( var_i == 3 ) { var_r = var_1 ; var_g = var_2 ; var_b = V    ; }
     else if ( var_i == 4 ) { var_r = var_3 ; var_g = var_1 ; var_b = V    ; }
     else                   { var_r = V     ; var_g = var_1 ; var_b = var_2; }
 
     R = var_r * 255;
     G = var_g * 255;
     B = var_b * 255;
  }
  return (R << 16) + (G << 8) + B;
}

That's in ActionScript 3, but it would be trivial to convert it to Javascript.

And to make a hue gradient, or "rainbow" gradient:

var g:Graphics = mDialog.graphics;

g.lineStyle(0, 0, 0);

var colors:Array = new Array();
var positions:Array = new Array();
var alphas:Array = new Array();
for (var h:Number = 0; h <= 1.01; h += .0833333)
{
  colors.push(getRBGfromHSV(h, 1.0, 1.0));
  positions.push(255 * h);
  alphas.push(1.0);
}

// The matrix will rotate my gradient 90 degrees
var gradientMatrix:Matrix = new Matrix();
gradientMatrix.createGradientBox(mHueRect.width, mHueRect.height, Math.PI/2.0);
gradientMatrix.translate(mHueRect.x, mHueRect.y);
g.beginGradientFill(GradientType.LINEAR, 
  colors,
  alphas, 
  positions, 
  gradientMatrix);
g.drawRect(mHueRect.x, mHueRect.y, mHueRect.width, mHueRect.height);
g.endFill();

I'm using Peter Huber's Pop3MimeClient code to check emails, and needed a way to parse the date string from the email header into a .NET DateTime object. The date format is defined in the email standard RFC822.

This is the code I wound up with:

// Date string in the header looks like this:
// Sun, 23 Sep 2007 09:24:59 -0500 (CDT)

// Remove the parenthetical reference to the time zone; this is redundant since
// we have the offset and time zone can't be represented in the format string
string sentString = Headers["Date"];
sentString = System.Text.RegularExpressions.Regex.Replace(sentString,
  "\\([a-zA-Z]*\\)", "");

return DateTime.ParseExact(sentString.Trim(), new string[] {
  "ddd, dd MMM yyyy HH:mm:ss zzzzz",
  "ddd, d MMM yyyy HH:mm:ss zzzzz"},
  System.Globalization.CultureInfo.InvariantCulture,
  System.Globalization.DateTimeStyles.AllowWhiteSpaces);

I used a regex to remove the (CDT) from the end of the string because there doesn't appear to be a wildcard character available for the DateTime format specifier. The two format strings are necessary because "dd" doesn't accept days less than ten.