SQLServer表字段默认值相关信息的获取方法

获取指定表的所有约束：

SELECT  OBJECT_NAME(so.id) AS tableName ,
        OBJECT_NAME(sc.constid)
FROM    sysconstraints SC
        INNER JOIN sysobjects SO ON sc.id = so.id
WHERE   OBJECT_NAME(so.id) = 'TableName'

获取指定表指定列的默认值：

SELECT  SCOM.text AS value
FROM    syscolumns SCOL
        LEFT JOIN syscomments SCOM ON SCOL.cdefault = SCOM.id
WHERE   SCOL.id = OBJECT_ID('TableName')
        AND SCOL.name = 'ColumnName'

获取更新指定表指定列默认值的脚本：

DECLARE @deleteSql VARCHAR(800)
DECLARE @insertSql VARCHAR(800)
SELECT  @deleteSql = 'ALTER TABLE [dbo].[KY_system_information] DROP CONSTRAINT ' + [name] ,
        @insertSql = 'ALTER TABLE [dbo].[KY_system_information] ADD CONSTRAINT DF__KY_system__payByYearAvg DEFAULT 0 FOR payByYearAvg'
FROM    sysobjects SO
WHERE   id = ( SELECT   cdefault
               FROM     syscolumns
               WHERE    id = OBJECT_ID('TableName')
                        AND name = 'ColumnName'
             ) 
PRINT @deleteSql
PRINT @insertSql

 

 

转载于:https://www.cnblogs.com/royliugc/archive/2013/02/05/2892823.html