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