1.查询一个默认值约束
select * from sys.default_constraints
where parent_object_id=object_id('LoginLog')
and parent_column_id=columnproperty(object_id('LoginLog'),'RecordTime','columnid')
使用：若想对某个字段添加默认值，但是你不确定是否已经有了默认值。你可以通过下面途径来删除可能有的默认值约束。 其中LoginLog为表名，RecordTime为列名。
declare @name varchar(50);
select @name=name from sys.default_constraints where parent_object_id=object_id('LoginLog') and parent_column_id=columnproperty(object_id('LoginLog'),'RecordTime','columnid');
select @name;
if @name is not null
begin
declare @sql varchar(500);
set @sql = 'alter table LoginLog drop constraint ' + @name;
exec(@sql);
print '删除约束名成功！'
end
然后增加表的字段默认值：
alter table LoginLog add DEFAULT (getdate()) for RecordTime with values;
下面是在另一个地方用到的默认值约束查询sql（TableName为表名，column1，column2是列名）：
select * FROM sys.default_constraints d
INNER JOIN sys.columns c
ON d.parent_column_id = c.column_id
WHERE d.parent_object_id = OBJECT_ID(N'dbo.TableName', N'U')
AND (c.name = 'column1' or c.name='column2')
2.查询表中的默认值
SELECT SO.NAME AS 'TableName', SC.NAME AS 'ColumnName', SM.TEXT AS 'DefaultValue'
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id
WHERE SO.xtype = 'U' ORDER BY SO.[name], SC.colid