下面实例,是将表'Table1','Table2','Table3'中的decima类型的数据全部更改为decimal(20,4)
其中'fn_split' 是分割函数,将输入的字符串按','分割成需要的结果集
BEGIN
IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID('Temp_A'))
DROP TABLE Temp_A
SELECT *,IDENTITY(INT,1,1) SerialNum INTO Temp_A
FROM dbo.fn_split('Table1','Table2','Table3', ',')
IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID('Temp_B'))
DROP TABLE Temp_B
CREATE TABLE Temp_B(ColumnName NVARCHAR(MAX),SerialNum INT IDENTITY)
--表名
DECLARE @ObjectName NVARCHAR(MAX)
--列名
DECLARE @ColumnName NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
---外循环
DECLARE @I1 INT =1
DECLARE @Q1 INT =(SELECT COUNT(*) FROM Temp_A)
---内循环
DECLARE @I2 INT =1
DECLARE @Q2 INT =0
--外循环
WHILE @I1 <= @Q1
BEGIN
DELETE Temp_B;
DBCC CHECKIDENT (Temp_B,rese