Get default value of a column using TSQL

Earlier while I was in another forum, someone had posted this question:

How do you find out if a column in a table has a default value?

Here's the query:

SELECT * FROM syscolumns WHERE id = object_id('yourtable') AND cdefault > 0

will give a list of columns that have default values set up. To get the VALUE of the default column,

SELECT * FROM syscomments WHERE id IN (SELECT cdefault FROM syscolumns
WHERE id = object_id('a') AND cdefault > 0)

will give the list of the columns that have default values and the column "CTEXT" has the actual default.

Hope this helps someone.


***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************

You just need to execute the stored procedure sp_columns <table_name>. The COLUMN_DEF column in the result table contains the information you want...

Also, remember that the TEXT column of syscomments is only NVARCHAR(4000), so in case somebody went out and wrote a big messy default constraint, then you're looking for more than a single row in syscomments.