34,576
社区成员
发帖
与我相关
我的任务
分享
--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(c.name) + '' varchar(20);'' + CHAR(10)+CHAR(13)
FROM syscolumns AS c
JOIN systypes AS t
ON c.xusertype = t.xusertype
AND c.id = OBJECT_ID(''?'') AND t.name = ''varchar''
AND c.length = 10;
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 t.name = ''varchar''
AND c.length = 10)';
--查字段
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;
--属性
exec sp_columns tb
--字段属性貌似没变
--try
CREATE TABLE tb(id int,val varchar(10),var2 varchar(50));
CREATE TABLE tb2(id int,val varchar(20),a字段名 varchar(50));
CREATE TABLE tb3(id int,a字段名 varchar(10),var2 varchar(10));
GO
EXEC sp_MsForeachTable
@command1 = N'
DECLARE @str NVARCHAR(1000),@str2 NVARCHAR(1000);
SELECT @str = '''',@str2 = '''';
SELECT @str = N''ALTER TABLE ? ALTER COLUMN ''
+ QUOTENAME(c.name) + '' varchar(20);'',
@str2 = N''EXEC sp_rename ''''?.''+QUOTENAME(c.name)+'''''',''''B字段名'''',''''COLUMN''''''
FROM syscolumns AS c
WHERE c.id = OBJECT_ID(''?'') AND c.name = ''a字段名''
EXEC(@str)
EXEC(@str2);
',
@whereand = N'
AND EXISTS(
SELECT * FROM syscolumns AS c
WHERE c.id = o.id
AND c.name = ''a字段名'')';
--查字段
SELECT
OBJECT_NAME(object_id) AS tbName,
name AS columnName
FROM sys.columns
WHERE OBJECT_NAME(object_id) IN('tb','tb2','tb3')
ORDER BY 1,column_id
GO
DROP TABLE tb,tb2,tb3;
/*
tbName columnName
-------------------- -------------------------
tb id
tb val
tb var2
tb2 id
tb2 val
tb2 B字段名
tb3 id
tb3 B字段名
tb3 var2
(9 行受影响)
*/
--try
CREATE TABLE tb(id int,val varchar(10),var2 varchar(50));
CREATE TABLE tb2(id int,val varchar(20),a字段名 varchar(50));
CREATE TABLE tb3(id int,a字段名 varchar(10),var2 varchar(10));
GO
EXEC sp_MsForeachTable
@command1 = N'
DECLARE @str NVARCHAR(1000),@str2 NVARCHAR(1000);
SELECT @str = '''',@str2 = '''';
SELECT @str = N''ALTER TABLE ? ALTER COLUMN ''
+ QUOTENAME(c.name) + '' varchar(20);'',
@str2 = N''EXEC sp_rename ''''?.''+QUOTENAME(c.name)+'''''',''''B字段名'''',''''COLUMN''''''
FROM syscolumns AS c
WHERE c.id = OBJECT_ID(''?'') AND c.name = ''a字段名''
EXEC(@str)
EXEC(@str2);
',
@whereand = N'
AND EXISTS(
SELECT * FROM syscolumns AS c
WHERE c.id = o.id
AND c.name = ''a字段名'')';
GO
--DROP TABLE tb,tb2,tb3;