SQL Server Performance Forum – Threads Archive

Default value

How would I get the default value of a column? (using T-SQL)

If you run sp_help <tablename> it’ll tell you what the defaults are for any columns which have defaults defined. Tom Pullen
DBA, Oxfam GB

If you want the actual value to work with, you can use this – with the usual precautions about querying system tables directly – just insert the table name and field name: SELECT Convert(int, substring(syscomments.text, 2, len(syscomments.text)-2))
from sysobjects as sysobjects_1
inner join sysobjects as sysobjects_2
on sysobjects_1.parent_obj = sysobjects_2.id
inner join syscomments
on sysobjects_1.id = syscomments.id
inner join syscolumns
on syscolumns.cdefault = sysobjects_1.id
where sysobjects_2.name = @TableName
and syscolumns.name = @FieldName Another caution: this query was set up for retrieving an integer default value, where you normally see brackets around the number, hence the SUBSTRING call. You’ll need to test the results to see how to handle other data types.

Also you can use sp_columns for further information on that column. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

adriaan, just what I need syscomments.
the missing piece in my query. thanks a lot!

]]>