49

How can I find out the default value of a column in a table using a SQL query?

By using this stored procedure:

sp_columns @tablename

I get some information on the columns of a particular table but the default value of the columns is missing, How can I get it?

shA.t
  • 16,580
  • 5
  • 54
  • 111
zad
  • 3,355
  • 2
  • 24
  • 25

3 Answers3

85

You can find the stored definition with the below (remember to adjust the column and table name to find to be ones relevant to your environment!)

SELECT object_definition(default_object_id) AS definition
FROM   sys.columns
WHERE  name      ='colname'
AND    object_id = object_id('dbo.tablename')
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    This returns nothing for me on sql server 2008... and I know for a fact that my schema / table / field names are correct, and that there is indeed a default. – Alkanshel Aug 01 '16 at 20:25
  • @Amalgovinus it definitely works. Are you running it in the right database context? Does the account you are running the query under have sufficient permissions to see the definition? – Martin Smith Aug 01 '16 at 20:27
  • Yeah, I didn't have permissions. In other circumstances Mssql is "nice" enough to tell you you can't access things like sysobjects, etc., but in this case it just pretends as if there's nothing to show if you're not using a database on which you have permission. I had to set a default database for this to work at all. http://stackoverflow.com/questions/362434/how-can-i-change-my-default-database-in-sql-server-without-using-ms-sql-server-m – Alkanshel Aug 01 '16 at 20:33
  • If a varchar's default is '', then it returns NULL for definition. – Dan H. Aug 10 '23 at 20:12
30

I use INFORMATION_SCHEMA table like this:

SELECT 
    TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_SCHEMA = @SchemaName 
  AND TABLE_NAME = @TableName
  AND COLUMN_NAME = @ColumnName;
shA.t
  • 16,580
  • 5
  • 54
  • 111
18

Use:

   SELECT so.name AS table_name, 
          sc.name AS column_name, 
          sm.text AS default_value
     FROM sys.sysobjects so
     JOIN sys.syscolumns sc ON sc.id = so.id
LEFT JOIN sys.syscomments sm ON sm.id = sc.cdefault
    WHERE so.xtype = 'U' 
      AND so.name = @yourtable
 ORDER BY so.[name], sc.colid
shA.t
  • 16,580
  • 5
  • 54
  • 111
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502