DOT Net Rocks live in Atlanta

This past Friday night, I had the opportunity to be present at the last stop on the .NET Rocks tour. For those who do not know, .NET rocks is the best pod cast available for .NET developers. I have been listening for a few months now and was so impressed, I went back and downloaded all the shows for the past couple of years. The hosts, Carl Franklin and Richard Campbell give the show a good mix of technology and entertainment, something that is missing among us computer geeks. I was excited to get to watch the recording of one of the shows live. The Road tour began 3 weeks ago on the west coast, hit 15 cities, and ended in Atlanta.

The guest was Brandon Watson, who is responsible for the Windows 7 phone developer experience. I felt extremely fortunate to be at this recording as I am one of the many developers gearing up to write apps for this phone. Being able to hear the podcast first was incredible and having the opportunity to talk to Brandon made the trip well worth it. This phone is going to be incredible from the looks of it and I think it will give the IPhone a run for its money. To find out more about the phone and Mr. Watson, you can visit his blog http://www.manyniches.com/ or if you are interested in developing for this device, visit the Microsoft page http://developer.windowsphone.com/ .

If you are developer in Microsoft technologies, you should also consider subscribing to the podcast available at http://www.dotnetrocks.com. The shows are informative and downright fun to listen to. During the show, they go over selected .NET framework classes (briefly), read emails from listeners and interview relevant guests. The shows are about an hour long (perfect for my commute at this time).

After the show was recorded, we were treated to Carl's musical talents as he performed "Hesitation Blues" as done by Hot Tuna. As a guitar player myself, I really enjoyed the song (thanks Carl) and headed to ITunes when I got home to get more of Hot Tuna's music. Also, I must admit, I experience a first when one of the door prizes was a Fender Jazz bass guitar, of which I was one coin toss away from winning. :( I have seen some off the wall door prizes but that has to be the most off-the-wall-iest. Another treat was a demonstration by Richard of the testing features Visual Studio Ultimate and an attempted demo of Silverlight Streaming. W.C. Fields once said never share the stage with an animal or a child. I think you could add a temperamental broadband connection to the list as well. This is truly a great tool and I am excited to be able to work with it.

All in all, I was several hours later getting home than I told my wife I was going to be but I was having such fun, I figured it was worth getting in trouble. I didn't get in trouble but if I had, it would have been worth it. Thanks to Carl and Richard for putting together a great show, taking it on the road, and letting us experience it in a way we couldn't have experienced it otherwise. Thanks for a fun evening and I'll keep listening to .NET Rocks.

Three levels of knowing; Three phases of training

Not too long ago, one of my students sent me an excerpt from a book 101 Things I Learned in Architecture School. While I am not an architect, this person thought the concept was generic enough to be applied to many other aspects of life. I agree. The basic text of this is …

Three Levels of Knowing

SIMPLICITY is the world view of the child or uninformed adult, fully engaged in his own experience and happily unaware of what lies beneath the surface of immediate reality.

COMPLEXITY characterizes the ordinary adult world view. It is characterized by an awareness of complex systems in nature and society but an inability to discern clarifying patterns and connections.

INFORMED SIMPLICITY is an enlightened view of reality. It is founded upon an ability to discern or create clarifying patterns within complex mixtures. Pattern recognition is a crucial skill for an architect, who must create a highly ordered building amid many competing and frequently nebulous design considerations.

This passage was applied to the realm of architecture. However, I agree that the concept has much further reaching implications than to tie it to one area. I liked it but wanted to think about it some more so I printed it off and hung it on my cube wall. This morning I walked in, read it and had an epiphany. For years, I have had a philosophy that martial arts training can be categorized in three distinct phases. I saw this morning that the three levels of knowing are very similar to the three phases of training. The phases I believe are distinct, but the lines between the phases and the length of the phases can change depending on the student. In any case, I believe a student’s training falls into one of these categories.

Phase 1 – Instinct / Reaction
Responses to aggression are driven by a simple reaction. Most of the time, the reaction is driven by instinct and is not the correct or most efficient reaction. A student automatically reacts and has very little control over that response. For example, if an attacker throws a punch, the student may just throw his hands up in some random fashion and hope for the best. If this works, it would be a complete accident. All of this happens at a sub-conscious level.

Phase one of our training is like the SIMPLICITY level of knowing above. Everything is simple and unencumbered by the need to know that something is beneath the surface, waiting to hurt us. When it pops up, we have no idea how to deal with it properly.

Phase 2 – Planning / Thinking
Responses to aggression are driven by thought, calculation and planning. We are trying to use what we learn in class to deal with the aggressive (verbal or physical) behavior. In this case, a student is trying to identity when that punch comes in, analyze it, and respond with the appropriate defense. This could be a block/counter punch, a parry, or an attempt to move out of the way and redirect. The student sees the complexity of the attack and is trying to deal with it the best way he knows how. If he is correct in identifying the attack and is quick enough to implement the correct defense, then he is successful. Otherwise, the defense is too slow and he is hit. All of this happens at a conscious level.

Phase two is like the COMPLEXITY level of knowing in that we are now aware of what is going on but in order to deal with it, we really have to think our way through things. As a result, sometimes we are unable to discern the patterns in of our attackers and unable to formulate the appropriate response in time.

Phase 3 – Instinct / Reaction
Responses to aggression are driven by a simple reaction. The difference this time is that after years of training the body, mind and spirit, reactions are now based on a “new” instinct and are usually much more efficient and effective reactions. This time, at the first hint of an attack, his body reacts without thinking. He has trained enough to know the correct response without thinking about it. He automatically reacts the right way. He is now back to reactions from the sub-conscious level but with an aspect of awareness blended in.

Phase three of our training is like the INFORMED SIMPLICITY level of knowing. In both cases, we have come full circle and are now able to deal with the complexities of our environment but from a deeper, simpler understanding. Your mind and body work together in harmony to recognize and defend against the attack without planning and even much thought. The defense is now a trained reaction.

The more I go through life, the more I learn that all things are interconnected and the number of “rules” that govern the universe is not as many as one would think. Concepts we learn in one area of our life are quite often relevant to other parts of our life as well.

Generate sequential numbers within a grouping in SQL Server

This article will discuss how to generate sequential numbers within a grouping in a SQL server table. I will say right up front, I know that set based operations are best and that any kind of cursor or looping should be avoided if possible. I have not been able to come up with a solution based purely on sets. This solution does not involve a cursor but it does involve a loop. Also, the data is being stored in a table variable so that limits what you can do. Having said all that, here is what I set out to accomplish. Suppose you have the following table structure.

Declare @Tab Table
(
  ID UniqueIdentifier not null default NewID(),
  Parent int not null,
  Description char(20),
  GrpRowCounter int
)

The ID uniquely identifies eac record. The Parent field is the field we are interested in grouping. The Description is there for flavor and the GrpRowCounter should be the value 1,2,3, etc... within each Parent grouping. Now that we have the table, let's load it up with sample data.

Insert Into @Tab (Parent, Description) Values (10,'ABC')
Insert Into @Tab (Parent, Description) Values (10,'DEF')
Insert Into @Tab (Parent, Description) Values (30,'GHI')
Insert Into @Tab (Parent, Description) Values (30,'JKL')
Insert Into @Tab (Parent, Description) Values (22,'MNO')
Insert Into @Tab (Parent, Description) Values (30,'PQR')
Insert Into @Tab (Parent, Description) Values (30,'STU')
Insert Into @Tab (Parent, Description) Values (10,'VWX')
Insert Into @Tab (Parent, Description) Values (22,'YZ1')
Insert Into @Tab (Parent, Description) Values (30,'234')

Notice that we did not populate the GrpRowCounter field just yet. The next step is to populate the table with that information. Since we are using a table variable, I selected to go with a loop structure. This loop structure will visit each of the rows in order and add the GrpRowCounter. For the code, we are going to follow this basic flow.

1. Visit each of the rows in order.
2. If the Parent code changes, reset our GrpRowCounter variable.
3. If the Parent code does not change, increment our GrpRowCounter.
4. Update the table.

Here is the code to do it.

Declare @ID uniqueidentifier  -- ID we are currently working with.
Declare @Parent int           -- parent we are currently working with.

Declare @TotalRows int      -- This holds the total number of rows in the table.
Declare @RowCounter int     -- This is my counter variable.
Declare @LastParent int     -- This holds the last looked up parent.  When a parent 
                            -- changes, the GroupRowCounter Resets
Declare @GrpRowCounter int  -- this holds the 1,2,3 per group.

-- this gets our maximum number of records so we know when to stop.
Select @TotalRows = COUNT(*) From @Tab   
-- Counter is initialized to 1.
Set @RowCounter = 1                      

While @RowCounter <= @TotalRows
Begin
    -- Get the current record.  We are using the Row_Number function to match the corect
    -- row back to the appropriate ID.  Also, notice we order on Parent first and then 
    -- on ID.
    Select @ID = T.ID, @Parent = T.Parent From @Tab T
    Inner Join (Select ID, ROW_NUMBER() Over (Order by Parent, ID) RowNum From @Tab) SubT
    On T.ID = SubT.ID
    Where SubT.RowNum = @RowCounter
    Order by Parent, T.ID
    
    -- Check to see if the Parent has changed.  On the first record, @LastParent 
    -- is null so it should fail and set the grp counter to 1
    IF @Parent = @LastParent 
        Set @GrpRowCounter = @GrpRowCounter + 1
    Else
        Set @GrpRowCounter = 1
    
    -- Now update the Record
    Update @Tab Set GrpRowCounter = @GrpRowCounter
    Where ID = @ID
    
    -- Now update the last parent
    Set @LastParent = @Parent
    
    -- Finally update our overall counter.
    Set @RowCounter = @RowCounter + 1
End

-- See the results
Select * From @Tab Order by Parent, ID

A Case for AikiTaiso

Introduction

Most every martial art that I have had the pleasure of practicing or at least observing
includes an aspect of training which involves sequences of movements designed to
practice just that; movement. In my karate study, one such exercise is called a
kata. Kata, in Japanese karate is a series of movements that mimic actual techniques
and build a scenario around an imaginary confrontation which is always (conveniently)
won by the performer of the kata. There are many papers and writings on the benefits
of kata, so it wouldn't be appropriate in this paper to go over all that. Instead,
this paper is about a similar concept that we find in our Aikido practice, Ki No
Taiso.

Ki No Taiso, or just taiso, is a collection of exercises that we perform at the
beginning of every class. According to AikidoJournal.Com, taiso is defined as “Aiki
exercises; refers to various movements and blending exercises practiced before training
in techniques; designed to teach the principle of aiki.” The taiso that most people
practice today originate with Koichi Tohei Sensei, one of O’Sensei’s senior students.
He was one of the first to see that O’Sensei was not only teaching techniques, but
that there were principles behind these techniques. Without a good understanding
of the principles, the techniques would never make much sense. To this end, he developed
many methods for teaching the four basic principles of ki, and one of the exercises
he used was taiso.

Not all aikido schools perform these exercises, and in my opinion, they are missing
out on a very important aspect of martial arts practice. The exercises that we perform
comprise many of the movements that we find in the majority of our techniques. By
practicing these movements ahead of time, we can teach the body the fundamentals
in a more controlled environment. This way, when the stress of the attack comes,
the body already knows about proper footwork, balance, posture, and other physical
aspects basic to performing aikido techniques. Also, from a more esoteric point
of view, these exercises help to train our spiritual side so that we can harness
the power of ki and more effectively execute the techniques the way they were designed
to be executed.

When I first started Aikido, I hated the taiso. I admit it without shame. I hated
it so much that I contemplated coming to class late to avoid it. Unfortunately,
I have met several students who do seem to show up late to class, seemingly just
to avoid these exercises. To miss this part of class is to be incomplete as a practitioner
of Aikido. Over the years, I have learned to see their value. Do I like them? The
answer to that is no not really. I suppose I should clarify that. I don't dislike
them but they are not my favorite part of class. I do, however, understand that
they play a vital role in my aikido training and for that, I am happy to practice
taiso and learn what they have to teach me.

What is Taiso?

Taiso is a set of exercises performed by the student while standing alone or in
a line. These exercises are simple, repetitive movements that reinforce movements
that are taught in class. No one is grabbing or attacking at this point. It is just
you and your body, moving and feeling. For example, we go through a variety of wrist
stretches, which teach us what it feels like to have a wrist lock performed, at
the same time warming up our joints to receive these locks while minimizing risk
of injury. We have other movements that stress good posture while moving your hips,
legs and feet in the right way.

Our school has about 28 separate taiso that teach posture, stretching, concentration,
relaxation, movement, ki extension and other basic principles of aikido. Some of
the taiso are exactly like a corresponding technique, while others are simply movement
drills designed to help you learn to move from the hip. In all, they encompass many
of the principles we learn in class.

What are the benefits?

As a teacher, I have come to love the taiso as a teaching aide that make my job
easier. Let me give you an example. In our taiso, we do what I like to call the
Aiki-Two-Step. This movement is where you step forward with one foot, and then spin
to bring your now back foot around behind you, winding up facing the other way.
We do this with Ikkyo Undo, Kokyu Ho, Ude Furi and other techniques. When I start
to teach a technique that involves this foot work, I often notice people don’t understand
how I am moving the way I move. This is when I relate what we are doing in the technique
back to what they are doing in the taiso. At this point, I like to see the light
bulb go on. They now have a link between what they practice at the beginning of
class with what they are doing on the mat right then.

If we take all this to the next step, then what I hope to accomplish as a teacher
is to use the taiso to teach the basics. That way, when I am showing how to move
into Kote-gaeshi, I don’t have to focus so much on the footwork. I can show them
the specifics of a joint lock and how the hand positions relate to each other. I
also don’t have to worry about posture. If the taiso are done with appropriate posture,
then it is not as big of an issue during the practice of the techniques.

How should you practice Taiso:

When practicing taiso, it should be done with concentration and treated with as
much respect as a technique. Taiso is a vital part of your training. When going
through the motions, try focusing on the following things.

1. Move your feet properly.
   This is a great chance to see what your feet are doing. Remember, if your foot is
   2 inches off the ground or 2 feet off the ground, it is still off the ground. You
   are not stable when your feet are not touching the ground.


2. Watch your posture.
   Pay attention to the way your body is positioned during the practice. Make sure
   that you are not moving your head out from over your body and your body out from
   over your hips. The farther your body parts are away from your center, the harder
   it is to keep your balance.


3. Pay attention to the people around you. While working on these skills, you should use this time to practice being aware of the people around you. Awareness is probably one of the most important skills in any martial art. You should never tunnel in and tune out the rest of the class


4. Watch your breathing. One of the things that does us in before class is over is when we run
   out of steam. This is exacerbated by not breathing properly. During the taiso, you
   don’t have anyone attached so relax, settle and breathe. The more you practice proper
   breathing, the greater chance you will have of it happening when you are not thinking
   about it.


5. Do this outside of class. Most Aikido needs to be done with a partner. Taiso gives you the opportunity to improve your Aikido movements without the need for a partner. Use them to improve the basic principles and you will find that when you do the techniques in class, you will have a better understanding of them and consequently, have more fun in class.


6. Focus on the four basic principles. This is an opportune time to focus on practicing the four basic principles as outlined by Koiechi Tohei Sensei: Focus on the one point, Relax completely, Keep weight underside, and Extend Ki.
   

One saying that I have grown to embrace over the years, I learned from Shihan David
Isgett. He told me “Practice does not make perfect. Perfect practice makes perfect.
Practice makes permanent.” If we engage in less than perfect practice, then that
is what we will become. So the lesson here is to practice as close to perfection
as we can get, so that what becomes permanent is what we desire to become permanent.

What do others think of Taiso:

While putting together this paper, I have reviewed many posts online concerning
Taiso and found that there are varying opinions of whether or not this type of practice
is beneficial or worthy of our time. Understand that these exercises were developed
by one of O’sensei’s senior students in an effort to better understand and teach
the basic principles of aikido. Learn them well and your time in class will be much
better spent.

Other benefits:

Taiso also has other benefits in class. Some of them provide certain stretches that
help the student warm up. Other benefits include a good cardio vascular warm up.
I have even found some of the gentler movements, while holding your infant child,
can sooth them back to sleep at two-thirty in the morning. Learn to enjoy taiso
and your efforts will be rewarded.

Generating String Permutations Recursively in C#

This article will demonstrate a simple routine that uses recursion in C# to generate
a list of string permutations. I originally wrote this routine because I was
interested in a little bit larger project in which I thought a routine like this
would be useful. I had become interested in an online game where, given a
string of X number of characters, you see how many way you can combine the letters
in that string to make words. The program would go something like this.

1. Generate the permutations.
2. Break each permutation down into 3, 4, 5 ... X letter strings.
3. Check each break down against a dictionary, if in the dictionary, keep the word

So the first order of business was to generate every possible permutation of an
X character string. After working with a recursive routine, I managed to get
it down to a very simple few lines of code.

First, before going over the code, let's understand the concept. Let's
look at the permutations for a 2 character string. Let's say our string
is 'AB'. The possible permutations are

1. 'AB'
2. 'BA'

Very easy, we just switch the last two characters. Now if we want to get the
permutations for a 3 character string, Say 'ABC', that process is very simple
too.

1. Start with 'ABC'.
2. Swap the last two characters to get ACB
3. Now take the original string and move the A into the second to last position giving
   you BAC
4. Again Swap the last two characters giving you BCA
5. Now come back out and swap the first two characters giving you CBA
6. And finally swap the last two characters giving you CAB

If you want to do four characters, the process is the same. Permutate the
right most 2 characters, then swap in character 3 and do the permutations, then
swap in character 4 and do the permutations again. The beauty of the recursion
is that upon leaving one swapping operation, you can get back to an original string
(stored on the stack) and keep the permutations going. Since the calls use
the string lenght to determine how to proceed, you should be able to pass a string
of any length in and get the permutations. The only caveat is that the number
of permutations generated for any given string is exponential as the string get's
larger. The number of permutation is X ! given that X is the number of characters
in the original string. As you can see, it can get quite large pretty quick.

String Size	Number of Permutations	Time to Process
1	1 (1!)	Negligable
2	2 (2!)	Negligable
3	6 (3!)	Negligable
4	24 (4!)	Negligable
.....	.....
10	3,628,800 (10!)	7.27 Seconds
12	479,001,600 (12!)	Out of Memory error thrown.

The time to process above was done on a dual processor test machine running at 3
GHz with 2 Gig of internal memory. As you can see, with a short string, you
can generate many permutations. Get much more over 10 and you will start to
see some serious performance degradation. If you really need to generate permutations
on strings that large, consider disk storage.

Now that we have discussed the concept, let's look at the code. I have
encapsulated all of the code into one class. Here is the listing for that
class.

using System;
using System.Collections;

namespace Permutations
{
    // To save space, I have not used proper techniques of 
    // hiding attributes behind properties. There are
    // probably other things that haven't been done as well 
    // but I am just trying to show the concept.
    class PermutateString
    {
        // Holds the final array of permutations
        public ArrayList AL = new ArrayList();

        public PermutateString(string Source)
        {
            Perm("", Source);
        }

        private void Perm(string Beginning, string Shuffle)
        {
            // By the time the Shuffle length get's down to 1, 
            // we have another permutation to store.
            if (Shuffle.Length <= 1)
                AL.Add(Beginning + Shuffle);
            else
            {
                for (int Ndx = 0; Ndx < Shuffle.Length; Ndx++)
                {
                    // here, we move the character at the ndx position
                    // to the front of the line, then copy in the first 
                    // ndx characters of the string and then the characters
                    // after that. so if NDX is at 3 in the following string,

                    // then the string ABCDEF would become DABCEF. This loop
                    // pulls each postion out to the front of the line and then
                    // calls the Perm operation again.
                    Shuffle = Shuffle.Substring(Ndx, 1) +
                        Shuffle.Substring(0, Ndx) +
                        Shuffle.Substring(Ndx + 1);
              
                    // Now that we have moved the shuffled characters around a bit,
                    // we call the perm function again moving one of the characters back
                    
                    // to the beginning string.
                    Perm(Beginning + Shuffle.Substring(0, 1),
                        Shuffle.Substring(1));
                }
            }
        }
    }
}

It may be a little difficult to follow so the best thing to do is to copy the
code into your project and run it through the debugger. As you trace the
values of Beginning and Shuffle, you should see the logic unfold. Hope you
enjoyed this.

Mio Moov 500 - My thoughts and impressions.

I have been thinking about it and thinking about it and I have finally entered the age of the GPS enabled. The biggest factor against me purchasing a GPS system was indeed the price. Most of the units that were even halfway useful were cost prohibitive. I had traveled with several friends who had GPS systems and I really thought they were a wonderful invention. Well, this past Christmas and Birthday (they are very close together for me), I received some cash so I renewed my search for the perfect GPS. While out shopping with my wife, I stopped into one of the Radio Shack stores in the local mall and noticed they were having a GPS sale. I looked over the models they had; Garmin, Sony, Tom Tom, etc... but the good ones were all still too expensive ($299 and up). Then I see this model with a funny name, Mio (Me Oh). There was a nice, easy to see screen at 4.7" and the price was $129. This was definitely more my speed. So, off I went to research this particular unit but I didn't have much time as the sale was ending in 3 days. After doing my due diligence on Google, I found the people were pretty much happy with their Mio GPS systems. I decided to take a plunge with this one and so far, after one week of playing with it, I am not disappointed. In fact, I am so happy with it, I am inspired to share this with the rest of the internet world.

Here is what I think of the unit. Before I get started, this is not an article intended to give you the technical specifications of the device. Instead, I am giving you my opinion of it. If I include any specs, it is in that context. If I get a spec wrong, then it is wrong, look at the documentation for the correct spec.

Goods:
These are the things that I liked about the system. This is hardly an exhaustive list of the features.

• The system starts up and is ready to use right out of the box (with a little battery charging)
  The screen is easy to read. The screen is 4.7" I think and it is very clear and easy to read.
• The voice prompts are mostly accurate and very timely. I like the progression of warnings before a turn. I have had only one problem with this on I75/85 as it joins through Atlanta. The unit didn't handle this as I thought it should be everything else has been great.
• The resolution of where you are is mind bogglingly accurate. I can zoom in on the "me" icon and it places me on the map with incredible accuracy. In fact, close to my house is an interstate overpass. The DOT built a bridge right beside is so they could tear down the old bridge and rebuild it. If I am on the new bridge, only 20 feet or so from the old bridge, the unit thinks I am on the expressway and begins to recalculate my route. this can be annoying but is a logical effect of the bridge not being where the software thinks it is.
• The unit is very configurable. There are lots of configuration options including nighttime display, route planning, how much time/distance is favored, avoiding toll roads, avoiding freeways or not, and the list goes on.

Bads:
While I didn't find too much bad with it, I did find some things that I think could use improvement. Also, this may be that I just haven't figured out how to do this yet.

• Panning, Zooming and level of detail - I found that when you are on a trip, it is difficult to "pan ahead" and look at turns. If you zoom out, then you loose much of the road detail.
• The unit doesn't have a built in compass. It seems that it determines direction solely on movement so if you are in a parking lot, you actually have to hit the road to find out which way is which. I think a compass would be a nice addition.

Overall:
I am very impressed with this unit. I have programmed in all my normal destinations and it get's me there the quickest way. I have even found a few places where I didn't know where I was going and the trip was flawless. I am sure as I use it more, I may find some problems but for the most part, it is accurate and very easy to use.

Suggestion:
In parting, I would have this suggestion. When you first get a GPS, I would use it right out of the box, even if you already know where you are going. This way, you become familiar with the unit and learn its quirks and how to operate it. The last thing you need is OJT when you are lost. Also, you may want to be aware that if you have roadwork going on, the trip planner doesn't take that into consideration so a little thinking may be required on your part to get around road closings. That is all and I hope this information has been helpful.

Brute Force Data Find Method for SQL Server

On several occasions, I have been tasked with finding some miscellaneous piece of information in a database. Usually, it is some Third party database that I am not that familiar with. For example, someone says, “I need to know where all the occurrences of a particular person’s email appears in this database.” My first approach was to try and find all the fields that might have EMAIL or MAIL or something like that in the title. I quickly found out that not all database designers are created equal. Standards are lacking and consistency is foreign word. To that end, I decided to create a brute force method for finding data. I may not be the quickest but it let’s someone else do the work; namely the database engine.

I am going to be using the Information_Schema views for this exercise. Since they are ANSI standard views, they should be compatible with future versions of SQL server as well. I try to stay away from system tables as there is no guarantee that they will remain consistent between versions. The way I decided to approach this was to query the Information_Schema.Columns view in order to retrieve a complete listing of tables and columns in the database. I use the results of this view to build another script which will actually do the work. This script I will call the “build” script. We will use the build script to build the “query” script.

The build script will consist of 3 parts; 1) create the table variable to hold the results, 2) populate that table with the results and 3) retrieve the values from the variable. Part 1 looks like this.

Build Script Part One
Set NoCount ON
Print 'Set NoCount On'
Print ''
Print 'Declare @Results Table '
Print '('
Print ' TableName varchar(50),'
Print ' FieldName varChar(50),'
Print ' FieldLength int,'
Print ' DataValue varChar(2000)'
Print ')'
Print ''

If we set the results screen in query analyzer to TEXT, then when we run this, it will generate the declaration much like you see it here. This will build the first part of the query script to declare the table value we will then populate. Part 2 will generate the statements to populate this. It looks like this.

Build Script Part Two
Select 'Insert Into @Results Select ''' + Table_Name + ''' as TableName, ''' +
Column_Name + ''' AS FieldName, ' +
Convert(varchar,Character_maximum_length) + ' as FieldLength, [' +
Column_Name + '] as DataValue ' +
'From [' + Table_Schema + '].[' + Table_Name + '] ' +
'Where Upper([' + Column_Name + ']) Like Upper(''%Prestwick%'')'
From Information_Schema.Columns
Where Data_Type in ('char', 'varchar','nvarchar','nchar') And
Character_maximum_length >= 9
and Table_Schema = 'dbo'

Let’s examine this code bit by bit. We are going to generate Insert statements. According to our @Results table from above, we are going to need 4 pieces of information. We will need the Table Name, the Field Name, the Field Length, and the actual data value where we found what we are looking for. As you can see from the code above, that is exactly what we are extracting from the Information_Schema.Columns view. We are using the meta data in the database to build insert statements that will return to query the data. On line 6, we specify the selection criteria for the “query” script. To eliminate and CASE issues, we will convert the value of the column as well as the value of the TextToFind to Upper case. You can massage this part to better fit your needs. If you know the value is exact, you can change the ‘LIKE’ to an ‘=’. In this case, I am looking for an address that contains the word Prestwick. I need to know the table and column. In the last part of the build query , I use the meta data in the columns view to eliminate some of the field. If I know that I am looking for text, I can immediately eliminate all the numeric, bit and date fields. If I know the text is at least 12 characters, there is no need to query any Char(6) fields. When this portion is run agains AdventureWorksDW database, here are some of the results.

Insert Into @Results Select 'DimCustomer' as TableName, 'CustomerAlternateKey' AS FieldName, 15 as FieldLength, [CustomerAlternateKey] as DataValue From [dbo].[DimCustomer] Where Upper([CustomerAlternateKey]) Like Upper('%Prestwick%')

Insert Into @Results Select 'DimCustomer' as TableName, 'FirstName' AS FieldName, 50 as FieldLength, [FirstName] as DataValue From [dbo].[DimCustomer] Where Upper([FirstName]) Like Upper('%Prestwick%')

Insert Into @Results Select 'DimCustomer' as TableName, 'MiddleName' AS FieldName, 50 as FieldLength, [MiddleName] as DataValue From [dbo].[DimCustomer] Where Upper([MiddleName]) Like Upper('%Prestwick%')

Insert Into @Results Select 'DimCustomer' as TableName, 'LastName' AS FieldName, 50 as FieldLength, [LastName] as DataValue From [dbo].[DimCustomer] Where Upper([LastName]) Like Upper('%Prestwick%')

Insert Into @Results Select 'DimCustomer' as TableName, 'Suffix' AS FieldName, 10 as FieldLength, [Suffix] as DataValue From [dbo].[DimCustomer] Where Upper([Suffix]) Like Upper('%Prestwick%')

Of course there are more records that fit this criteria but for now, I am only showing a few for example. Now that we have the declaration and the insert statements generated, the last part of the script is the retrieval of those records. That looks like this.

Build Script Part Three
Print ''
Print 'Select Distinct * From @Results'

If we take all three sections of the Build script and combine them together and run it, we can generate a script that will query each potential field in the database for the value you are looking for. Remember, to generate the “query” script, you will need to set the query analyzer to return text results, not grid results. The next step is to copy the contents of the results of the Build script and paste them into a new query window. If I run the entire script on the AdventureWorksDW database, I can now find out which tables and fields contain this value. The actual Query script that this generates will look at over 140 fields and let you know what it finds. The results of my script are as follows.


TableName FieldName FieldLength DataValue
----------------- ------------- ----------- ---------------------
DimCustomer AddressLine1 120 1588 Prestwick Drive
DimCustomer AddressLine1 120 1841 Prestwick Drive
DimCustomer AddressLine1 120 4152 Prestwick Drive
DimCustomer AddressLine1 120 4998 Prestwick Ave.
DimCustomer AddressLine1 120 5259 Prestwick Dr.
DimCustomer AddressLine1 120 6208 Prestwick Dr.
DimCustomer AddressLine1 120 6709 Prestwick Ave
DimCustomer AddressLine1 120 7568 Prestwick Court
DimCustomer AddressLine1 120 8079 Prestwick Drive
DimCustomer AddressLine1 120 9271 Prestwick Ave.
DimCustomer AddressLine1 120 9875 Prestwick Court
ProspectiveBuyer AddressLine1 120 1841 Prestwick Drive
ProspectiveBuyer AddressLine1 120 8079 Prestwick Drive
ProspectiveBuyer AddressLine1 120 9875 Prestwick Court
vTargetMail AddressLine1 120 1588 Prestwick Drive
vTargetMail AddressLine1 120 1841 Prestwick Drive
vTargetMail AddressLine1 120 4152 Prestwick Drive
vTargetMail AddressLine1 120 4998 Prestwick Ave.
vTargetMail AddressLine1 120 5259 Prestwick Dr.
vTargetMail AddressLine1 120 6208 Prestwick Dr.
vTargetMail AddressLine1 120 6709 Prestwick Ave
vTargetMail AddressLine1 120 7568 Prestwick Court
vTargetMail AddressLine1 120 8079 Prestwick Drive
vTargetMail AddressLine1 120 9271 Prestwick Ave.
vTargetMail AddressLine1 120 9875 Prestwick Court


From here I can see that the value ‘Prestwick’ appears in 3 different tables. This gives me a headstart on doing the rest of my research.