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;

This is Sunflower, or as we typically call her "Sunny".  As you know back in November of 2005 we lost our dear Maya.  A few weeks later we just couldn't bear not having cats in our house and went to a local pet shelter to adopt three kittens.  There were a number of things we didn't like about the way the shelter was run, but in the end we had three wonderful cats that we loved--affectionate, playful, and apparently healthy.

It turns out that the shelter let us down in that department too.  It turns out that Sunflower, the littlest kitty, the one bearing the name I gave her the day she came home, is infected with Feline Leukemia Virus (FeLV), and it has entered an advanced stage even though she is only 3 years old.  Our cats have never had contact with any strange cats since the shelter, and live entirely indoors--our veterinarian has informed us that Sunny almost certainly came from the shelter with the infection.  They went on to say that they have seen over the last couple years increasing numbers of sick cats from that particular shelter and no longer recommend it to people looking to adopt cats.

The shelter was supposed to have tested all of its cats for FeLV, and the tests are very reliable and hard to screw up.  But clearly they were remiss with regard to testing Sunny.  And now Sunny has been living (infected) in close quarters with our other cats for three years.  We've had the other cats tested and miraculously they are negative, so we immediately had them innoculated for the virus.

But poor Sunny is dying.  She will not eat, barely drinks at all, and hardly moves.  When she does move she staggers badly.  She still purrs when I pet her but she is clearly not going to last much longer.  I wouldn't be surprised if she dies tonite.  Given she is suffering a condition which is essentially incurable, I'll be taking her to the vet tomorrow to release her, barely 3 years old, from her suffering.  It's breaking my heart.

Our three kitties have bonded with each other over the years, and are always together, it's going to be very different with Sunny gone.  We really fell in love with our trio of cats, and it became quite a ritual to say "kittycats, kittycats, 1, 2, 3" whenever we saw them.  Or if we only saw a pair we would say "kittycats, kittycats, 1, 2, where's number 3?"  Now there will be no number 3.

My daughter took it very hard when Maya had to be put down.  When the kittens came home, she immediately took to Sunny, as Sunny was the littlest kitty.  And strangely, Sunny took to Neya, perhaps because Neya was the littlest person.  So while the other cats would sleep with Pat and I or follow us around, Sunny would always be found not far from our daughter, sleeping in her bed with her or nearby.  If I had to pick the worst cat to lose, Sunny would be the biggest loss.

It's just not fair.

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.