MS SQL批量修改数据库的表的字段类型

--try
CREATE TABLE tb(id int,val varchar(10),var2 varchar(50));
CREATE TABLE tb2(id int,val varchar(20),var2 varchar(50));
CREATE TABLE tb3(id int,val varchar(10),var2 varchar(10));
GO
--查字段
SELECT
OBJECT_NAME(object_id) AS tbName,
name AS columnName,
max_length
FROM sys.columns
WHERE OBJECT_NAME(object_id) IN('tb','tb2','tb3')
ORDER BY 1,column_id
-----

EXEC sp_MsForeachTable
@command1 = N'
DECLARE @str NVARCHAR(MAX);
SET @str = '''';

SELECT @str = @str + N''ALTER TABLE ? ALTER COLUMN ''
+ QUOTENAME() + '' numeric(8, 2);''
FROM syscolumns AS c
JOIN systypes AS t
ON c.xusertype = t.xusertype
AND c.id = OBJECT_ID(''?'') AND = ''money''
AND c.length = 8;

EXEC(@str);',
@whereand = N'
AND EXISTS(
SELECT * FROM syscolumns AS c
JOIN systypes AS t
ON c.xusertype = t.xusertype
AND c.id = o.id
AND = ''money''
AND c.length = 8)';

--查字段
SELECT
OBJECT_NAME(object_id) AS tbName,
name AS columnName,
max_length
FROM sys.columns
WHERE OBJECT_NAME(object_id) IN('tb','tb2','tb3')
ORDER BY 1,column_id

GO
DROP TABLE tb,tb2,tb3;