已知修改列名的语句为
EXEC sp_rename 'customers.[contact title] ', 'title ', 'COLUMN '现我要实现这样的功能:根据列中的关键词来修改该列的名称,如该列中包含有“万元”这个词的,该列名称则修改为“注册资金”,否则不修改列名,请问用SQL语句如何实现?谢谢。
EXEC sp_rename 'customers.[contact title] ', 'title ', 'COLUMN '现我要实现这样的功能:根据列中的关键词来修改该列的名称,如该列中包含有“万元”这个词的,该列名称则修改为“注册资金”,否则不修改列名,请问用SQL语句如何实现?谢谢。
begin
EXEC sp_rename 'customers.[contact title] ', 'title ', 'COLUMN '
END
begin
EXEC sp_rename 'customers.[contact title] ', 'title ', 'COLUMN '
end
begin
EXEC sp_rename 'customers.[contact title] ', 'title ', 'COLUMN '
END
GODECLARE @tablename NVARCHAR(255), @colname NVARCHAR(255), @sql NVARCHAR(MAX)DECLARE cursor_field CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT tablename=a.name, colname=b.name
FROM sys.tables a
INNER JOIN sys.columns b
ON a.object_id = b.object_id
WHERE a.type = 'u'
AND b.name LIKE '%万元%'OPEN cursor_field
FETCH NEXT FROM cursor_field INTO @tablename, @colnameWHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = N'EXEC sp_rename '''+ QUOTENAME(@tablename) + '.'+QUOTENAME(@colname) + +''', ''注册资金'', ''COLUMN'''
EXEC(@sql)
FETCH NEXT FROM cursor_field INTO @tablename, @colname
ENDCLOSE cursor_field
DEALLOCATE cursor_field
GO
DECLARE @tablename NVARCHAR(255), @colname NVARCHAR(255), @sql NVARCHAR(MAX)
DECLARE cursor_field CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT tablename=a.name, colname=b.name
FROM sys.tables a
INNER JOIN sys.columns b
ON a.object_id = b.object_id
INNER JOIN sys.types c
ON b.user_type_id = c.user_type_id
WHERE a.type = 'u'
AND c.name IN('varchar','nvarchar','char','nchar') --楼主要根据需要扩充
OPEN cursor_field
FETCH NEXT FROM cursor_field INTO @tablename, @colname
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = N'if exists (select 1 from '+QUOTENAME(@tablename)+' where '+QUOTENAME(@colname)+' like ''%万元%'')
EXEC sp_rename '''+ QUOTENAME(@tablename) + '.'+QUOTENAME(@colname) + +''', ''注册资金'', ''COLUMN'''
--PRINT @sql
EXEC(@sql)
FETCH NEXT FROM cursor_field INTO @tablename, @colname
END
CLOSE cursor_field
DEALLOCATE cursor_field