重命名所有表中的列 - SQL [英] rename a column in all the tables - SQL

问题描述

我需要重命名数据库中所有表中的列.所以所有表中的列OldColumn"必须重命名为NewColumn"

I need to rename a column in all tables in my database. so a column 'OldColumn' has to be renamed to 'NewColumn' in all the tables

我可以使用此查询获取包含此列的表的列表:

I could get list of tables that has this column using this query:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME= <Column Name>

但是如何在所有表中尽可能简单地重命名它并且不必编写游标?

But how can i rename it in all tables as simple as possible and don't have to write a cursor?

推荐答案

当然，您不需要为此使用游标.您可以使用 sys.columns 和 sys.objects 来生成动态 sql.然后简单地执行它.一旦您对动态 sql 感到满意，请随时取消对最后一行的注释.

Of course you don't need a cursor for this. You can use sys.columns and sys.objects to generate dynamic sql. Then simply execute it. Once you are satisfied the dynamic sql is what you want feel free to uncomment the last line.

----请注意！！！！！！----如果您更改列名，您的视图、存储过程、函数等都会被破坏.

----BE WARNED!!!!---- If you change column names your views, stored procedures, functions etc will all be broken.

declare @CurrentColumnName sysname = 'asdf'
    , @NewColumnName sysname = 'qwer'
    , @SQL nvarchar(MAX) = ''

select @SQL = @SQL + 'EXEC sp_rename ''' + o.name + '.' + c.name + ''', ''' + @NewColumnName + ''', ''COLUMN'';'
from sys.columns c
join sys.objects o on o.object_id = c.object_id
where c.name = @CurrentColumnName

select @SQL

--exec sp_executesql @sql

这篇关于重命名所有表中的列 - SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！