Every Table Related to My Table, Recursively

Someone asked me a few months ago how they could find every table in a database that was related to one particular table.  They wanted to know what tables referenced this table via foreign keys, what tables referenced the referencing tables, and so on, from 1 to n.

There’s meta data in SQL Server that can help you do this.  Here’s a SQL statement that will find (and number by distance from the original table) all the foreign keys that reference the table, all the tables that reference that table, etc.

;

 WITH fkey 

 as (

      select   constraint_id = f.object_id

        ,      constraint_name = f.name

        ,      parent_object_id

        ,      parent_name = object_name(f.parent_object_id)

        ,      referenced_object_id

        ,      referenced_object_name = object_name(f.referenced_object_id)

      from     sys.foreign_keys f

 )

 ,  recurse

 as (

      select   depth = 1

         ,     *

      from     fkey

      where    referenced_object_name = 'myTable'  -- <-- use this to filter results.

      union all

      select   depth = recurse.depth + 1

        ,      fkey.*

      from     fkey

         join  recurse 

            on fkey.referenced_object_id = recurse.parent_object_id

 )

 ,  recurseWithFields

 as (

      select   r.*

            ,  parent_column_id

            ,  parent_column_name = p_ac.name

            ,  referenced_column_id

            ,  reference_column_name = r_ac.name

      from     recurse r

          join sys.foreign_key_columns fc

            on r.constraint_id = fc.constraint_object_id

          join sys.all_columns p_ac

            on fc.parent_column_id = p_ac.column_id

            and fc.parent_object_id = p_ac.object_id

          join sys.all_columns r_ac

            on  fc.referenced_column_id = r_ac.column_id

            and fc.referenced_object_id = r_ac.object_id

 )

 select *

 from   recurseWithFields

This will work – but bear in mind that this a list of tables that reference “MyTable”, and doesn’t include tables that “MyTable” references.  To get a list of all tables that reference “MyTable” and vice-versa, recursively, you’d have to do this differently.

If you’d like to see that, let me know:  I can work on that.

A Common Misconception

I see this pattern a lot, particularly on Stack Overflow but also in some of the apps I'm maintaining.

DataSet ds = new DataSet();
ds = dataLayer.GetExampleData();

I was puzzled as to why the programmer would initialize a variable to a new object instance when the very next step is to set the variable to something else.  The DataSet created in the first line is thrown away and never used.  It seems mildly counter-productive.

But I think I’ve figured it out.  The programmer must have been under the impression that when a variable is set to the result of a method, and that method returns null, the variable retains its previous value. 

That isn’t true.

Let me restate that for emphasis:  THIS ISN’T TRUE.  When you set a value to the result of a method call, your variable is set to whatever that method call returns.  If the method call returns null, your variable is now set to null.   Period.  End of story.

I don’t know a language that works otherwise, although I’m sure (this being a big universe) that there must be one or two that do.   The key point, though, is:  THIS ISN’T HOW C# WORKS. 

Bad Idea Challenge #3

This looks like okay code, doesn’t it?

   public IEnumerable<County> GetCounties(int stateId)
   {
       using (var ef = new TopologyEntities())
       {
           return ef.Counties.Where(c => c.stateId == stateId);
       }            
   }

After all, it’s using the using pattern with the IDisposable-implementing EF data context.   Isn’t that what you’re supposed to do?

But:  the following code will throw an error. 

       var counties = GetCounties(10);
       var count = counties.Count();

Do you see why?

(For bonus points) Which line of this code will throw the exception? Why?

Useful Things to Know, #430: Casting to an Enum

We’re all familiar with defining an Enum.  In C#, they look like this:

public enum States
{
    Open,
    InUse,
    Closed
}

It’s understood by most people that Enum values are an alternate way of representing numeric constants.  Sometimes this is made explicit, as in the following:

public enum States
{
    Open = 1,
    InUse  = 2,
    Closed = 3
}

So when you assign to a value of type States, Intellisense will offer you a choice of three values:  States.Open, States.InUse, and States.Closed.

Are those the only values a variable of type States can have?

Actually, no. 

You can assign any integer to variable of an Enum, regardless of whether it’s defined as one of the available constants.  With the example above, the following:

States state = (States) 35;

… would be perfectly fine. Neither the compiler nor the runtime prevent this from happening.

So if your code looked like this:

if(state == States.Open)
{ 
   // do something
}
else if (state == States.InUse)
{
   // do something else
else {
   // assume the value must be States.Closed
}

… you have a potential bug. 

Bad Idea Challenge #2

Consider the following database table:

CREATE TABLE Code
(
  Code char(4) PRIMARY KEY
  Description varchar(50)
)

It’s a brand new table in a database nobody is using but you.  You add it to an Entity Framework model and add a value to it, as in this test:

using(Model1DataContext dc = new Model1DataContext())
{
  // Verify the table is empty:
  Assert.AreEqual(0, dc.Codes.Count());

  // Add and save a new record:
  Code rcd = new Code() { Code = "X", Description = "A description" };
  dc.Codes.Add(rcd);
  dc.SaveChanges();

  // That was one record. How many records do you think get returned?
  Assert.AreEqual(1, dc.Codes.Count());
}

Surprise:  you’ll get 2 records back. 

Do you see why?

Hint:  if you inspect the two records, they’ll look superficially the same.  But there’ll be one key (ahem) difference. 

ETA:  No, the semantic key is not the Bad Idea.  But you’re close. 

On sitting down with a new stack of books

The danger of reading programming books is that you may well learn you have been diligently doing the wrong things.

That can sting.  But the danger of NOT reading programming books is that you might never find that out.

Embrace the buzzsaw.  Keep reading. 

Useful Things to Know, #429: Recursive Anonymous Functions

If you’ve ever tried to code an anonymous function that calls itself, you may have concluded that it can’t be done.  After all, trying the following …

 Func<int, int> collatz = (n) =>
 {
      if (n == 1)
          return 0;
      if (n % 2 == 0)
          return collatz(n/2) + 1;
      else
          return collatz(3 * n + 1) + 1;
 };

… leads to the message:  “Error:  Use of unassigned local variable ‘collatz’”.

And this makes sense:  the delegate “collatz” doesn’t have a value until after the execution of the assignment statement in which you’re referencing it!

But the error message contains the key to making it work.  The message says that “collatz” is unassigned.  So in order to use it in an expression, assign it an initial value before assigning it your recursive anonymous function.

Func<int, int> collatz = null;
collatz = (n) =>
{
    if (n == 1)
        return 0;
    if (n % 2 == 0)
        return collatz(n/2) + 1;
    else
        return collatz(3 * n + 1) + 1;
};

(If you’re wondering what problem that function could possibly solve, check here.)