Sunday, July 19, 2015

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 =
        ,      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 =
            ,  referenced_column_id
            ,  reference_column_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.

Labels: ,

Monday, December 30, 2013

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. 

Sunday, July 21, 2013

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?


Sunday, March 24, 2013

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

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. 


Wednesday, March 13, 2013

Bad Idea Challenge #2

Consider the following database table:

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" };

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


Sunday, February 24, 2013

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. 

Saturday, February 09, 2013

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;
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;
return collatz(3 * n + 1) + 1;

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