I spent several hours mining Google and tweaking to get this query right.  This has never been a technical blog, but considering what a pain this was to chase down, I figured I should post it here for others.

This query will list all the tables and columns in a SQL Server 2000 database, including column data type, length, precision, scale, nullable flag, if the column is part of a unique constraint, if the column is part of the primary key, the foreign table referenced by the column if it is a foreign key, and the default value of the column if it has one.

The output is suitable for pasting into Excel or Word to create database reports.  (Yes I'm sure there are 3rd party tools, and stored procedures to get all this info, but what I needed was ONE query which did it all and returned it as this query does.)

select T.name as TableName, C.colorder, C.name as ColumnName, TY.name as datatype,
       c.length, c.prec, c.scale, c.isnullable, UO.name as UniqueConstraint,
       IX.name as PKConstraint, SR.name as ReferencesTable, SM.Text as DefaultValue

  from sysobjects as T
 inner join syscolumns as C on (C.id = T.id)
 inner join systypes as TY on (TY.xtype = C.xtype)
  left join sysindexkeys as K on C.colid = K.colid and K.id=T.id
  left join sysindexes as I on I.id=T.id and I.indid=K.indid
  left join sysobjects as IX on IX.parent_obj=T.id and IX.name = I.name and IX.xtype='PK'
  left join sysconstraints as CO on (C.colid=CO.colid and CO.id=T.id)
  left join sysforeignkeys as FK on (T.id = FK.fkeyid and FK.constid=CO.constid)
  left join sysobjects as SR on (SR.id = FK.rkeyid)
  left join syscomments SM on C.cdefault = SM.id 
  left join sysindexkeys as UK on C.colid = UK.colid and UK.id=T.id
  left join sysindexes as UI on UI.id=T.id and UI.indid=K.indid
  left join sysobjects as UO on (UO.parent_obj=T.id and UO.name=UI.name and UO.xtype='UQ')

where T.xtype = 'U'
order by T.name, C.colorder;

So I celebrated Pi Day with some friends yesterday and had a nice time.  The pies and treats were delicious.  Sadly I didn't bring a cherry pie for Julie, I will have to remember to do that next year.  The blueberry pie was great, the chicken pot pie was delicious, and though the glutin-free lemon meringue was very runny, it still tasted fine.

We played games (I had a nice long game of Carcassonne, and other people played Bohnanza), made up silly jokes and songs, and just had a very nice afternoon.  I got to meet some new friends, and spend a little time with some old ones. And I ate WAY too much pie.

This morning my idle mind wrote a little song parody based on the day which I decided to share here:

You made me blueberry,
Apple and pecan;
Wet meringue with lemon goo
And pot pie tastes so grand.

(The bigger, the better)
Our tummies fully crammed...
Collected from around the world
we'll eat them if we can!

Pies pies pies yeah!
Pies pies pies yeah!
Pies pies pies yeah!
Pies pies pies yeah!

The pound cake was delicious,
And whoopie pies were fine.
But poor Julie got no cherry,
What a waste of time.

Big ones and small ones,
Look lovely to our eyes,
Tantalizing on the plate,
Not so much on our thighs...

Pies pies pies yeah!
Pies pies pies yeah!
Pies pies pies yeah!
Pies pies pies yeah!

Go back to your larder,
Fetch me another plate,
My pancreas is still alive,
And I'm not the sort to wait.

The bigger, the better,
And some are glutin free,
So what if you don't eat your share,
More for me and me and me and me and me and

Pies pies pies yeah! (We're gonna eat you.)
Pies pies pies yeah! (Not too discreet to.)
Pies pies pies yeah! (Fat like a B-2.)
Pies pies pies yeah!

Oh you know I know...

Pies pies pies yeah!
Pies pies pies yeah!
Pies pies pies yeah!
Pies pies pies yeah!

Oh you know I know...

Pies pies pies yeah!
Pies pies pies yeah!
Pies pies pies yeah!
Pies pies pies yeah!

In a single payer system, the cost of healthcare drops dramatically.  Yes it's true taxes go up, but at this point?  They're going up anyway.  The economic stimulus our country needs is going to have to be paid for, and we are all going to pay for it.  Combine this with the 30 years this country has frittered away when it should have been building a new energy grid, green technologies, maintaining infrastructure, and so on, and it's clear, the party is over.

And by "the party is over" I'm referring to those artificially low taxes that our conservative friends still think are too high.

I am sick and tired of listening to gloom-and-doom naysayers fiddling about long lines, delays, and taxes while Rome burns.  I have friends who are suffering right now simply because they cannot get the help they need due to lack of coverage.

Single-payer health care works in many countries around the world, some better than others.  I have enough faith in American ingenuity that we can make it work here.  But it does mean (AFAIC) the end (or serious curtailing) of health insurance companies that have risen to wealth on their two core tenets--1. collect premiums, and 2. don't pay benefits.

For those who quake in fear at the notion?  We tried it your way for decades and it sucks.  I'm ready for something new.