SQL sever 通过存储实现全库数据修改(全库搜索 需要修改数据)

修改一批数据、无法断定存在于哪个表中、

大体情况如下：

odata列为旧数据、需要修改的数据

ndata列为新数据、更新后的数据

需要将全库所有内容为odata列的数据更新为ndata列

无法断定具体哪张表存在相同数据、

创建存储过程查找需要修改的字段

creat PROCEDURE [dbo].[proc_uptmp_ywsj]

AS

BEGIN

SET NOCOUNT ON;

--变量定义

--计算过程执行时长

DECLARE @starttime DATETIME;

DECLARE @totaltime INT;

--流程控制(成功失败)

DECLARE @Result INT;

--执行影响行数

DECLARE @ROW INT;

--错误日志

DECLARE @ErrorMessage VARCHAR(1000);

--变量赋值

SET @starttime = GETDATE();

SET @Result = 0;

--创建临时表

CREATE TABLE #tmp_pdchg_z

(

table_name VARCHAR(100) ,

filed_name VARCHAR(100)

);

--全库筛选可能存在需修改数据的列、数据类型等条件根据实际情况选择

INSERT INTO #tmp_pdchg_z

( table_name ,

filed_name

)

SELECT a.table_name ,

a.filed_name

FROM ( SELECT d.name table_name ,

a.colorder 字段序号 ,

a.name filed_name ,

( CASE WHEN COLUMNPROPERTY(a.id, a.name,

'IsIdentity') = 1

THEN '√'

ELSE ''

END ) 标识 ,

( CASE WHEN ( SELECT COUNT(*)

FROM sysobjects

WHERE ( name IN (

SELECT

name

FROM

sysindexes

WHERE

( id = a.id )

AND ( indid IN (

SELECT

indid

FROM

sysindexkeys

WHERE

( id = a.id )

AND ( colid IN (

SELECT

colid

FROM

syscolumns

WHERE

( id = a.id )

AND ( name = a.name ) ) ) ) ) ) )

AND ( xtype = 'PK' )

) > 0 THEN '√'

ELSE ''

END ) 主键 ,

b.name 类型 ,

a.length 占用字节数 ,

COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度 ,

ISNULL(COLUMNPROPERTY(a.id, a.name,

'Scale'), 0) AS 小数位数 ,

( CASE WHEN a.isnullable = 1 THEN '√'

ELSE ''

END ) 允许空 ,

ISNULL(e.text, '') 默认值 ,

ISNULL(g.[value], ' ') AS [说明]

FROM syscolumns a

LEFT JOIN systypes b ON a.xtype = b.xusertype

INNER JOIN sysobjects d ON a.id = d.id

AND d.xtype = 'U'

AND d.name <> 'dtproperties'

LEFT JOIN syscomments e ON a.cdefault = e.id

LEFT JOIN sys.extended_properties g ON a.id = g.major_id

AND a.colid = g.minor_id

LEFT JOIN sys.extended_properties f ON d.id = f.class

AND f.minor_id = 0

WHERE b.name IS NOT NULL

AND d.name IN (

SELECT name

FROM sysobjects

WHERE xtype = 'U'

AND name NOT LIKE '%API%'

AND name NOT LIKE '%Workflow%'

AND name NOT LIKE '%Membership%'

AND name NOT LIKE '%LOG%' )

) a

WHERE a.主键 =''

AND a.默认值 =''

AND a.类型 ='varchar'

OR a.类型 = 'nvarchar';

--SELECT *

--FROM #tmp_pdchg_z

DECLARE @tb_f_yj VARCHAR(500);

DECLARE @table_name VARCHAR(50);

DECLARE @filed_name VARCHAR(50);

DECLARE @T TABLE ( cc VARCHAR(20) );

--开启事务

-- BEGIN TRAN;

-- BEGIN TRY

-----游标循环判断可能存在需修改数据的列是否存在需修改数据

DECLARE tb_f_cursor CURSOR

FOR

SELECT 'SELECT top 1 ' + filed_name + ' FROM ' + table_name

+ ' a WHERE EXISTS (SELECT 1 FROM tmp_ywsj WHERE odata=a.'

+ filed_name + ' );' ,

table_name ,

filed_name

FROM #tmp_pdchg_z;

OPEN tb_f_cursor;

FETCH NEXT FROM tb_f_cursor INTO @tb_f_yj, @table_name,

@filed_name;

WHILE @@fetch_status = 0

--计算逻辑

BEGIN

INSERT INTO @T

EXEC ( @tb_f_yj

);

IF EXISTS ( SELECT 1

FROM @T )

---将实际存在需修改数据的表名、列名插入表中

BEGIN

INSERT INTO uptmp_ywsj

VALUES ( @table_name, @filed_name );

END;

DELETE FROM @T;

FETCH NEXT FROM tb_f_cursor INTO @tb_f_yj, @table_name,@filed_name;

END;

CLOSE tb_f_cursor;

DEALLOCATE tb_f_cursor;

--计算总时长

SET @totaltime = DATEDIFF(SECOND, @starttime, GETDATE());

--END TRY

--BEGIN CATCH

-- IF @@TRANCOUNT > 0

-- BEGIN

-- ROLLBACK TRAN;

-- SELECT @ErrorMessage = '操作发生异常:'

-- + CAST(ERROR_NUMBER() AS VARCHAR) + ','

-- + ERROR_MESSAGE() ,

-- @Result = -1;

-- END;

--END CATCH;

--IF @@TRANCOUNT > 0

-- AND @Result = 0

-- BEGIN

-- COMMIT TRAN;

-- END;

--IF @@TRANCOUNT > 0

-- AND @Result <> 0

-- BEGIN

-- ROLLBACK TRAN;

-- END;

--SELECT @b AS b;

--删除临时表

IF EXISTS ( SELECT *

FROM tempdb..sysobjects

WHERE id = OBJECT_ID('tempdb..#tmp_aa') )

BEGIN

DROP TABLE #tmp_aa;

END;

--记录日志

--INSERT INTO tb_SYSLOGS(Logger,Logtime,LogIP,Logname,LogTotalTime,LevelCode,Message,LogSQL)

-- SELECT 'Templete' ,

-- GETDATE() ,

-- '::1' ,

-- 'sa' ,

-- @totaltime ,

-- 'proc_uptmp_pdchg' ,

-- '' ,

-- '影响行数' + CAST(@ROW AS VARCHAR(10)) + ',' + @ErrorMessage;

END;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• 51
• 52
• 53
• 54
• 55
• 56
• 57
• 58
• 59
• 60
• 61
• 62
• 63
• 64
• 65
• 66
• 67
• 68
• 69
• 70
• 71
• 72
• 73
• 74
• 75
• 76
• 77
• 78
• 79
• 80
• 81
• 82
• 83
• 84
• 85
• 86
• 87
• 88
• 89
• 90
• 91
• 92
• 93
• 94
• 95
• 96
• 97
• 98
• 99
• 100
• 101
• 102
• 103
• 104
• 105
• 106
• 107
• 108
• 109
• 110
• 111
• 112
• 113
• 114
• 115
• 116
• 117
• 118
• 119
• 120
• 121
• 122
• 123
• 124
• 125
• 126
• 127
• 128
• 129
• 130
• 131
• 132
• 133
• 134
• 135
• 136
• 137
• 138
• 139
• 140
• 141
• 142
• 143
• 144
• 145
• 146
• 147
• 148
• 149
• 150
• 151
• 152
• 153
• 154
• 155
• 156
• 157
• 158
• 159
• 160
• 161
• 162
• 163
• 164
• 165
• 166
• 167
• 168
• 169
• 170
• 171
• 172
• 173
• 174
• 175
• 176
• 177
• 178
• 179
• 180
• 181
• 182
• 183
• 184
• 185
• 186
• 187
• 188
• 189
• 190
• 191
• 192
• 193
• 194
• 195
• 196
• 197
• 198
• 199
• 200
• 201
• 202
• 203

备注: 其中

a.主键 =”

AND a.默认值 =”

AND a.类型 =’varchar’

OR a.类型 = ‘nvarchar’

根据实际情况筛选

创建此过程中需要用到的表

------存放需修改数据的表

CREATE TABLE [dbo].[tmp_ywsj](

odata [varchar](100) NULL,

ndata [varchar](100) NULL

) ON [PRIMARY] ;

-----创建表用于存放数据库中实际需修改的列

CREATE TABLE uptmp_ywsj

(table_name VARCHAR(100),

filed_name VARCHAR(100));

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

执行存储过程

EXEC proc_uptmp_ywsj

• 1

实际需修改的列会写入uptmp_ywsj表中

根据uptmp_ywsj 生成update脚本

SELECT

'CREATE NONCLUSTERED INDEX [NON-'+table_name+'_'+filed_name+'] ON '+table_name+' ('+filed_name+');',

'UPDATE SET A.'+filed_name+' = B.ndata, a.UpdatedAt = GETDATE() FROM dbo.'+table_name+' A, dbo.tmp_ywsj B WHERE B.odata = a.'+filed_name+';' ,

'DROP INDEX [NON-'+table_name+'_'+filed_name+'] ON dbo.'+table_name+';'

FROM uptmp_ywsj

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

执行查询结果