Thursday, February 22, 2007

How To Find Out Which Columns Have Defaults And What Those Default Values Are

Okay so many many moons ago you created a bunch of tables and those tables have columns of course. You want to know how to find the columns that have defaults.There are a couple of ways to do thisBelow is a list:

See what happens when you don't specify a name (we will do this later)? You will get wacky names like these: DF__blah__id__15A53433 and DF__blah__SomeDate__1699586CInstead of specifying the default when creating the table use an alter table add constraint statement.Let's see this in action.

And last we have sp_helpYou can use sp_help in SQL Server 2000(you can also use it in SQL server 2005 but it doesn't return the defaults )Execute the followingsp_help'blah'

The defaults will be in the last resultset (the one where the first column name = constraint_type)

And last but not least did you notice that we had CURRENT_TIMESTAMP but when we queried the table we saw GETDATE() This is kind of strange since CURRENT_TIMESTAMP is ANSI complaint but GETDATE() is not

I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you"