SQL Server Performance

Get default value of a column using TSQL

Discussion in 'Contribute Your SQL Server Scripts' started by ndinakar, Feb 10, 2006.

  1. ndinakar Member

    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.
  2. Madhivanan Moderator

    SELECT cols.name, c_obj.name ,t_obj.name FROM sysobjects t_obj, sysobjects c_obj, syscolumns cols
    WHERE cols.id = t_obj.id AND c_obj.id = cols.cdefault AND c_obj.xtype = 'D'


    Failing to plan is Planning to fail
  3. aruncaddy New Member

    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...
  4. Adriaan New Member

    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.

Share This Page