情境是某個作用類似 key的欄位要重新編號…
老實說這需求現在想還是很奇怪，程式又不……嗯，略過感想直接寫方法好了，一句說完就是 dynamic sql，多說一點就是拿字串當 SQL指令執行。
DECLARE @SQL NVARCHAR(MAX)
, @TABLE_NAME VARCHAR(50)
, @COLUMN_NAME VARCHAR(50)
, @PK_COLUMN_NAME VARCHAR(500)
DECLARE TableCursor Cursor FOR
/* ************************************ */
Select TABLE_NAME, COLUMN_NAME, STUFF((
SELECT ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkTable
WHERE pkTable.TABLE_NAME = colTable.TABLE_NAME
FOR XML PATH('')
), 1, 2, '') PK
From INFORMATION_SCHEMA.COLUMNS colTable
Where column_name LIKE '%column1_name%' OR column_name LIKE '%column2_name%'
/* ************************************ */
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TABLE_NAME, @COLUMN_NAME, @PK_COLUMN_NAME
WHILE(@@FETCH_STATUS <> -1 )
BEGIN
-- 重新編號的邏輯得自己寫，這裡做的只是 loop每個 table, column.
-- 先 select出來 & print只是方便存檔備查。
SET @SQL = N'
select {1}, left({1}, 6) + ''9'' + right({1}, 7) [New_{1}], {2}
from {0}
where {1} is not null and len({1}) = 14 and convert(int, right({1}, 8)) > 1000;
update {0} set {1} = left({1}, 6) + ''9'' + right({1}, 7)
where {1} is not null and len({1}) = 14 and convert(int, right({1}, 8)) > 1000;
'
SET @SQL = REPLACE(@SQL, '{0}', @TABLE_NAME)
SET @SQL = REPLACE(@SQL, '{1}', @COLUMN_NAME)
SET @SQL = REPLACE(@SQL, '{2}', @PK_COLUMN_NAME)
PRINT @SQL
EXECUTE sp_executesql @SQL
FETCH NEXT FROM TableCursor INTO @TABLE_NAME, @COLUMN_NAME, @PK_COLUMN_NAME
END
CLOSE TableCursor
DEALLOCATE TableCursor