修正或添加字段默认值约束的名称
旧项目中的数据库约束名称不规范，写了个脚本重新修改。
1. 名称重新修改为已有的默认值约束的名称为'DF_' + 表名 + 字段名；
2. 没有设置默认值约束的列设置默认值，字符串为空，数值为0；
3. 时间字段除了addtime不加默认值；
1 --修正或添加字段默认值约束的名称为DF_表名_字段 2 3 /* 4 SELECT 5 tabName= LOWER(O.name), --表名 6 colName=LOWER(C.name), --字段名 7 coltype= LOWER(T.name), --类型 8 dfVal=ISNULL(D.definition,N''), --默认值 9 dfName=ISNULL(D.name,N'') --默认值约束名称 10 ,dfSName = ('DF_' + O.name + '_' + C.name) --默认值标准名称 11 FROM sys.columns C 12 INNER JOIN sys.objects O 13 ON C.[object_id]=O.[object_id] 14 AND O.type='U' 15 AND O.is_ms_shipped=0 16 INNER JOIN sys.types T 17 ON C.user_type_id=T.user_type_id 18 LEFT JOIN sys.default_constraints D 19 ON C.[object_id]=D.parent_object_id 20 AND C.column_id=D.parent_column_id 21 AND C.default_object_id=D.[object_id] 22 WHERE C.is_identity=0 --非标识列 23 AND C.is_computed= 0 --非计算列 24 And O.name IN ( 'fxs_eshop_task','pro_list','Ck_areaKuc','express_yt_region','Ck_Proc_List','fxgw_sys_config','fxs_duizhang','Ck_CustomsDocking') --表名 25 --And C.name IN ('Sort_Id','Type_Code','Hot_Exp_Printer_Name','Exp_Printer_Name','Confirm_Time','addTime','settlementbegintime','hone')--字段名 26 AND (D.definition LIKE '%NULL%') --获取为null的约束 27 ORDER BY O.name,C.name 28 */ 29 --修正已有的默认值约束的名称为'DF_' + 表名 + 字段名 30 BEGIN TRAN 31 DECLARE @NumCategory INT ,@StringCategory INT,@DatetimeCategory INT,@GUIDCategory INT ;--定义字段类型分类数值，字符串，日期，GUID 32 SELECT @NumCategory=1,@StringCategory = 2,@DatetimeCategory = 4,@GUIDCategory = 8; 33 declare Cols_Cursor cursor FOR 34 SELECT 35 tabName= LOWER(O.name), --表名 36 colName=LOWER(C.name), --字段名 37 coltype= LOWER(T.name), --类型 38 dfVal=ISNULL(D.definition,N''), --默认值 39 dfName=ISNULL(D.name,N'') --默认值约束名称 40 ,dfSName = ('DF_' + O.name + '_' + C.name) --默认值标准名称 41 42 FROM sys.columns C 43 INNER JOIN sys.objects O 44 ON C.[object_id]=O.[object_id] 45 AND O.type='U' 46 AND O.is_ms_shipped=0 47 INNER JOIN sys.types T 48 ON C.user_type_id=T.user_type_id 49 LEFT JOIN sys.default_constraints D 50 ON C.[object_id]=D.parent_object_id 51 AND C.column_id=D.parent_column_id 52 AND C.default_object_id=D.[object_id] 53 WHERE C.is_identity=0 --非标识列 54 AND C.is_computed= 0 --非计算列 55 --And O.name IN ( 'fxs_eshop_task','pro_list','Ck_areaKuc','express_yt_region','Ck_Proc_List','fxgw_sys_config','fxs_duizhang','Ck_CustomsDocking') --表名 56 --And C.name IN ('Sort_Id','Type_Code','Hot_Exp_Printer_Name','Exp_Printer_Name','Confirm_Time','addTime','settlementbegintime','hone')--字段名 57 --AND (D.definition LIKE '%NULL%') --获取为null的约束 58 ORDER BY O.name,C.name 59 open Cols_Cursor 60 declare @tabName varchar(200), @colName varchar(200), @coltype varchar(200), @dfVal varchar(200), @dfName varchar(200), @dfSName varchar(200) 61 62 fetch next from Cols_Cursor into @tabName , @colName , @coltype , @dfVal , @dfName , @dfSName 63 64 while(@@fetch_status=0) 65 begin 66 --print @tabName + @dfSName 67 DECLARE @todo INT,@defaultVal VARCHAR(200),@sql VARCHAR(2000),@colCategory INT; 68 SELECT @todo = 0,@defaultVal = 'NULL',@sql = '',@colCategory = 0; 69 IF( @coltype = 'money' OR @coltype = 'real' OR @coltype = 'int' OR @coltype = 'decimal' OR @coltype = 'smallint' OR @coltype = 'numeric' OR @coltype = 'tinyint' OR @coltype = 'float' OR @coltype = 'bigint' OR @coltype = 'bit') --字符串类型 70 BEGIN 71 SELECT @todo = 1,@defaultVal = '0',@colCategory = @NumCategory; 72 END 73 ELSE IF ( (@coltype = 'datetime' OR @coltype = 'smalldatetime' OR @coltype = 'datetime2') AND @colName = 'addtime')--有些特殊的时间字段，比如发货时间、审核时间不要有默认值 74 BEGIN 75 SELECT @todo = 1,@defaultVal = 'GETDATE()',@colCategory = @DatetimeCategory; 76 END 77 ELSE IF ( @coltype = 'uniqueidentifier') 78 BEGIN 79 SELECT @todo = 1,@defaultVal = 'NEWID()',@colCategory = @GUIDCategory; 80 END 81 ELSE IF ( @coltype = 'nchar' OR @coltype = 'char' OR @coltype = 'nvarchar' OR @coltype = 'varchar' OR @coltype = 'text' OR @coltype = 'ntext' ) 82 BEGIN 83 SELECT @todo = 1,@defaultVal = '''''',@colCategory = @StringCategory; 84 END 85 IF(@dfName = '')--无默认值约束 86 BEGIN 87 IF (@todo = 1) 88 BEGIN 89 SELECT @sql = 'IF NOT exists (select * from sysobjects where id = object_id(N''[' + @dfSName +']'')) ALTER TABLE [' + @tabName + '] ADD CONSTRAINT '+@dfSName+' DEFAULT '+ @defaultVal +' FOR ['+@colName+']' ; 90 SELECT @sql = @sql + ' Else ALTER TABLE [' + @tabName + '] ADD CONSTRAINT '+ (@dfSName+ '1') +' DEFAULT '+ @defaultVal +' FOR ['+@colName+'] ' ;--考虑到某些表和字段加起来正好一样,所以再后面加1； 91 92 print '增加约束,' +@tabName + '表' + @colName + '无默认值约束,类型为' + @coltype + '，执行sql:' + @sql; 93 EXEC(@sql); 94 END 95 ELSE 96 BEGIN 97 print '无约束但不增加,' +@tabName + '表' + @colName + '无默认值约束,类型为' + @coltype + '，不能修改；'; 98 END 99 END 100 ELSE 101 BEGIN 102 --约束名称不等于'DF_' + 表名 + 字段名，默认值取原有的 103 IF(@dfName <> @dfSName 104 OR ( @dfVal LIKE '%(NULL)%' AND @colCategory IN (@StringCategory,@NumCategory)) 105 OR (@dfVal LIKE '%('''')%' AND @colCategory = @NumCategory) --修正数值类型的字段默认值设置为空字符串的问题 106 ) 107 BEGIN 108 IF( NOT (@dfVal LIKE '%(NULL)%' AND @colCategory IN (@StringCategory,@NumCategory)) ) --字段串和数据类型的字段如果默认值为null，要重新修正为默认值''和0，其他情况用原字段的默认值； 109 SELECT @defaultVal = @dfVal; 110 IF(@dfVal LIKE '%('''')%' AND @colCategory = @NumCategory) --修正数值类型的字段默认值设置为空字符串的问题 111 SELECT @defaultVal = '0'; 112 SELECT @sql = 'ALTER TABLE [' + @tabName + '] DROP CONSTRAINT ['+@dfName + '];' --删除已有约束 113 SELECT @sql = @sql + ' IF NOT exists (select * from sysobjects where id = object_id(N''[' + @dfSName +']'')) ALTER TABLE [' + @tabName + '] ADD CONSTRAINT '+@dfSName+' DEFAULT '+ @defaultVal +' FOR ['+@colName+'] ' ; 114 SELECT @sql = @sql + ' Else ALTER TABLE [' + @tabName + '] ADD CONSTRAINT '+ (@dfSName+ '2') +' DEFAULT '+ @defaultVal +' FOR ['+@colName+'] ' ;--考虑到某些表和字段加起来正好一样,所以再后面加2； 115 116 print ('重建约束,' +@tabName + '表' + @colName + '类型：' + LTRIM(STR(@colCategory))+ '有默认值约束' + @dfName + ',默认值' +@dfVal + ';删除后重建约束,执行sql:' + @sql); 117 EXEC(@sql); -- 118 END 119 ELSE 120 BEGIN 121 print '维持约束不变,' + @tabName + '表' + @colName + '有默认值约束' + @dfName + ',默认值' +@dfVal + '；类型：' + LTRIM(STR(@colCategory)); 122 END 123 END 124 fetch next from Cols_Cursor into @tabName , @colName , @coltype , @dfVal , @dfName , @dfSName 125 end 126 close Cols_Cursor 127 deallocate Cols_Cursor 128 ROLLBACK
