Search all columns of all tables for a pattern

Today at work someone wanted to know how to search all columns of all tables for a pattern.  The code below uses a recursive select to generate dynamic SQL which will search all varchar columns for the specified pattern.  Not all columns are able to be cast to varchar.
declare @searchString varchar(max)
set @searchString = '%dealer sites%' --the pattern to search for


declare @queryToRun varchar(max) --internal variable.

--this variable needs to be non null otherwise it will make the recursive select return null.

set @queryToRun = ''

--create a statement to search all columns of all tables for the text.

SELECT @queryToRun = @queryToRun + 'select '''+t.table_name +'.'+c.column_name+''' from ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ' where ' + QUOTENAME(c.column_name) + ' like '''+ @searchString + ''' union '

                  FROM INFORMATION_SCHEMA.TABLES t JOIN

                  INFORMATION_SCHEMA.COLUMNS c on t.table_name = c.table_name

                  WHERE             TABLE_TYPE = 'BASE TABLE' and data_type='varchar'

--trim off the last union statement.

select @queryToRun = left(@queryToRun, datalength(@queryToRun) - datalength('union '))

--run the query.

exec (@queryToRun)

T4 - Text Template Transformation Toolkit

At the Lansing Day of .NET I saw a presentation about Microsoft's T4 (Text Templating Transformation Toolkit).  At the time I couldn't think of where I could use this tool to be more DRY because dry folks go over big at parties :)  Some of the examples given were in generating documentation and in generating a data layer.  There are tools specifically for those tasks so that didn't sit well with me.


Our current product is a web product and we sometimes use the session to pass data between our pages.  It was a lot of redundant typing and I figured T4 could help.


The template takes a list of strings and makes a static class exposing the strings as constants.  These strings are used as keys to pull data out of the session.  Some of the keys need post processing when the data is pulled out.  In this implementation I'm doing that via reflection but after writing it I figure a dictionary could do the trick and be faster.


Put this text in a .tt file and save it.  It will generate a .cs file shown below:




<#@ template language="C#v3.5" debug="true" #>
<#@ output extension="cs" #>

namespace tt
{
    /// <summary>
    /// Used to store keys to lookup session data.  This should ensure we don't have collisions and can easily refactor.
    /// </summary>
    /// <remarks>
    /// This class is autogenerated by SessionKeys.tt.  Do not modify this file!
    /// </remarks>
    public static class SessionKeys
    {
<# foreach ( string constant in GetConstants())
{
string[] inputs = constant.Split(',');

for (int i = 1; i < inputs.Length; i++)
                {
string attribute = inputs[i].Trim();
if(attribute.Length > 0)
{
WriteLine("[" + inputs[i].Trim() + "]");
}
                }

WriteLine("public const string " + inputs[0].ToUpper() + " = \"" + inputs[0] + "\";");
}
#>

    }
}

<#+
///<summary>
/// provides constants for SessionKeys.cs.  Some constants need attributes to be processed.
/// This is done via Attributes in the namespace UrbanScience.Si2.Module.LeadReporting.Utilities.
/// The format of the strings in this list should be IDENTIFIER, Attribute, Attribute, Attribute....
///</summary>
private string[] GetConstants()
{

return new string[]
{
"Name,InputAllowsWildCard",
"StartDate",
"EndDate",
"StateList, CleanMultipleInput",
};
}
#>

Here is the generated class:

namespace tt

{

    /// <summary>

    /// Used to store keys to lookup session data.  This should ensure we don't have collisions and can easily refactor.

    /// </summary>

    /// <remarks>

    /// This class is autogenerated by SessionKeys.tt.  Do not modify this file!

    /// </remarks>

    public static class SessionKeys

    {

            [InputAllowsWildCard]

public const string NAME = "Name";

public const string STARTDATE = "StartDate";

public const string ENDDATE = "EndDate";

[CleanMultipleInput]

public const string STATELIST = "StateList";

           

    }

}

Debugging win32 API errors

This blog post documents the methodology I used to diagnose a win32 API exception with a generic error message.  If you are in the same situation hopefully you'll find this helpful..

In April Tess Ferrandez blogged about Visualizing virtual memory usage and GC Heap.  I thought it would be cool if her code were modified to call Windbg programmatically and use a WPF frontend.  I found out Windbg uses dbgeng.dll!  Awesome!  I'll just write a managed wrapper around that.  While researching writing the wrapper I found CodePlex already had such a project called: mdbglib.  Great!  So I grabbed the code compiled and......Unhandled Exception!  No....... "First-chance exception at 0x7c9666c6 (ntdll.dll) in ASDumpAnalyzer.exe: 0xC0000139: Entry Point Not Found.".

My call stack looked like this: (bolded entries will be referenced later)

  ntdll.dll!_RtlRaiseStatus@4()  + 0x26 bytes
  ntdll.dll!_LdrpSnapThunk@32()  + 0x2a2b2 bytes
  ntdll.dll!_LdrpSnapIAT@16()  + 0xd9 bytes
  ntdll.dll!_LdrpHandleOneOldFormatImportDescriptor@16()  + 0x7a bytes
  ntdll.dll!_LdrpHandleOldFormatImportDescriptors@16()  + 0x2e bytes
  ntdll.dll!_LdrpWalkImportDescriptor@8()  + 0x11d bytes
  ntdll.dll!_LdrpLoadDll@24()  - 0x26c bytes
  ntdll.dll!_LdrLoadDll@16()  + 0x110 bytes
  kernel32.dll!_LoadLibraryExW@12()  + 0xc8 bytes
  mscorjit.dll!Compiler::impImportBlockCode()  + 0x5661 bytes
  mscorjit.dll!Compiler::impImportBlock()  + 0x59 bytes
  mscorjit.dll!Compiler::impImport()  + 0xb2 bytes
  mscorjit.dll!Compiler::fgImport()  + 0x20 bytes
  mscorjit.dll!Compiler::compCompile()  + 0xc bytes
  mscorjit.dll!Compiler::compCompile()  + 0x270 bytes
  mscorjit.dll!jitNativeCode()  + 0xa0 bytes
  mscorjit.dll!CILJit::compileMethod()  + 0x25 bytes
> ASDumpAnalyzer.exe!ASDumpAnalyzer.MainForm.MainForm_Load(object sender = {ASDumpAnalyzer.MainForm}, System.EventArgs e = {System.EventArgs}) Line 66 + 0x8 bytes C#

Based on the call stack I figured the code was throwing during JIT.  Looking at the method being jitted I was able to determine the Debuggee class was causing the Exception.  I figured the project was written on Windows Vista and was using a function that didn't exist in XP.  Looking around the CLI / C++ project nothing jumped out at me.  I decided to buy a C++ / CLI book, read about the win32 API calling convention: stdcall, and read about getting the parameters to LoadLibraryExW.  I also found out Visual Studio has  memory windows and a registers window.  I also found out that the ntdll.dll function names with a p in them are private, so I wouldn't be able to get their signature from MSDN like I could for Kernel32 LoadLibraryExW.

Armed with this knowledge I dug back in.

I put a breakpoint (bp) at {,,ntdll.dll}_LdrpSnapThunk@32.  This ended up getting hit all the time, so I put a hit count bp on {,,kernel32}_LoadLibraryExW@12 with a count of 59 because that is right before the app explodes.  I disabled my SnapThunk bp and enabled it after the LoadLibrary bp was hit.  Looking at the 32 bits on the stack using offsets from the esp register (esp, esp+4, esp+8........). and resolving the four 8 bit values addresses to memory locations I didn't see a function name like I was hoping.

So I started debugging assembly line by line.  After a few minutes I see 

7C917EE5  push        edi  

7C917EE6  push        eax  

7C917EE7  push        dword ptr [ebp+8] 

7C917EEA  push        dword ptr [esi+18h] 

7C917EED  push        ebx  

7C917EEE  call        _LdrpNameToOrdinal@20 (7C917EFDh)

I get very excited because there is a function name in esp when 7C917EEE executes!  This loop gets called a lot so I decide to put a bp on 7C917EEE and print the function name when it is called instead of breaking by putting this: {*(char**)esp}.  I hit F5 and watch function names fill up my output.  Right about the time I get my exception message I see DebugConnectWide in output.  Searching through the mdbglib project I see Debuggee.cpp line 56 has this call: Tools::CheckHR(DebugConnectWide(pwRemoteOptions, __uuidof(DbgClient), (void **)&debugClient ))  Comment that line rebuild, and no exception!

Recursive Selects

Last week at work I created some SQL Server functions to take a list of rows and turn them into a comma delimited string.  It feels like data formatting which should be done on the client side, but that was the method we went with.

It does show recursive selects though, which can also be used for dynamic cross tab queries.  At first this method didn't make any sense to me, but thinking about a select statement as a loop makes it make sense.

declare @siblings table (id int, firstName varchar(max))

insert into @siblings

      select 0, 'Jordan' UNION

      select 1, 'Mia'

declare @siblingString varchar(max)

set @siblingString = ''

select @siblingString = @siblingString + firstName + ', '

      from @siblings

if (datalength(@siblingString) > 2)

      select @siblingString = left(@siblingString, datalength(@siblingString) -2)

select @siblingString

Outputs 'Jordan, Mia'

Lansing Day of .NET 2009 Review

I had a great time at the Lansing Day of .NET Saturday.  I really enjoyed seeing everyone there, and we got fancy cool LDODN 2k9 beer tumblers.  There was a great after party at Jeff McWherter's that Eric Vogel helped me get to.  My favorite sessions were T4: Code Generation with Visual Studio 2008 by Steve Andrews and Improving our Craft: A Discussion on Software Estimation by Mike Eaton.

The T4 Code Generation facilities allow you to leverage the power of C# or VB.NET to write code generation scripts.  It also seemed straightforward to extend the code generators and also code generation hosts. The code generation kicks off by default when you save the file in Visual Studio, but one could write a custom host to execute it from a command line environment or during the build process..  The session showed some interesting scenarios for generating help documentation and a data layer using T4.  I don't plan on using T4 anytime soon, but I'm glad to know the tool exists and have had an overview of it's capabilities.  I've seen Steve Andrews present before and I like his style.  He mixes in keyboard shortcut tips and shows Visual Studio configuration options during the talks I've seen.

Mike Eaton's presentation on Software Estimation initiated some interesting discussion topics and thoughts to ponder.  He is an engaging speaker and had some funny cartoons.  Mike was especially adept at involving the audience in the presentation.