How do I see the default values of a table and its columns?

Question

I have a table that have default values for a column. How do I actually see the actual default values for each column ? Is it location in Information_Schema anywhere?

Answer 1

You ca get it from sys.columns

SELECT object_definition(default_object_id) AS default_value
FROM sys.columns
WHERE name ='col_name'
AND object_id = object_id('table_name')

If you want it for all of your columns, exclude the column name from you WHERE clause.

Answer 2

yes it is:

select TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT from INFORMATION_SCHEMA.COLUMNS where table_name = 'yourtablename'

Answer 3

You can use any of these,

This will give you every details of the table's structure.

EXEC sp_columns @table_name = N'Department', @table_owner = 'HumanResources';

See more about sp_columns.

Or you can 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