sql server : get default value of a column

Question

Hello, I execute a select to get the structure of a table. I want to get info about the columns like its name or if it's null or if it's primary key.. I do something like this

....sys.columns c...
c.precision,
c.scale,
c.is_nullable as isnullable,
c.default_object_id as columndefault,
c.is_computed as iscomputed,

but for default value i get the id..something like 454545454 but i want to get the value "xxxx". What is the table to search or what is the function to convert that id to the value. Thanks

Answer 1

You can do this (done a SELECT * just so you can see all the info available):

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE....

This includes a "COLUMN_DEFAULT" column in the resultset.

Answer 2

The property you want is called "cdefault".

http://sql-server-performance.com/Community/forums/p/20588/114944.aspx

Answer 3

Use

Select * From INFORMATION_SCHEMA.COLUMNS

there is a column called COLUMN_DEFAULT

Answer 4

'bills' is an example table

select 
COLUMN_DEFAULT              --default
,IS_NULLABLE                -- is nullable
,NUMERIC_PRECISION          --precision
,NUMERIC_PRECISION_RADIX    --decimal places
,NUMERIC_SCALE              --scale
,COLUMNPROPERTY(OBJECT_ID('bills'),COLUMN_NAME,'Iscomputed') AS ISCOMPUTED --is computed
 from INFORMATION_SCHEMA.columns where TABLE_name='bills'

 select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='bills' and CONSTRAINT_TYPE='PRIMARY KEY'