如何将一个MS SQL Server数据库中的所有表的列名更改为大写?

is there any sql statement used to change all column name to UPPER CASE for all tables in database? MS SQL Server.

是否有sql语句用于将数据库中的所有表的所有列名更改为大写?MS SQL Server。

I got a sql to do that, but not sure whether it`s correct.

我有一个sql来做这个，但不确定它是否正确。

run SQL below

运行以下SQL

select 'exec sp_rename '''+b.name+'.'+a.name+''','''+UPPER(a.name)+''',''column'''
from syscolumns a, sysobjects b 
where a.id=b.id and b.type='U' 
order by b.name

copy and execute the result above

复制并执行上面的结果

3 个解决方案

#1

If you are upgrading an application from SQL Server 2000 to a later edition, and you are struggeling with SQL Server case sensitivity, I would suggest you look into the SQL Server 2000 compatibility setting before you do drastic changes to the database.

如果您正在将一个应用程序从SQL Server 2000升级到一个较晚的版本，并且您正在为SQL Server case敏感性而苦恼，那么我建议您在对数据库进行重大更改之前，先研究一下SQL Server 2000兼容性设置。

In SQL Server 2008 Management Studio

在SQL Server 2008 Management Studio中

Right-click the database and select properties in the context menu
右键单击数据库并在上下文菜单中选择properties
Go to the Options page
到选项页面
In the third dropdown from the top. select Compatibility Level: SQL Server 2000
在第三个从上往下。选择兼容性级别:SQL Server 2000

At least that is time consuming.

至少，这很耗时。

Edit: Since it appears that OP is upgrading his database from SQL Server 2005 to a "new" database on SQL Server 2005, the above strategy might not be optimal.

编辑:由于OP似乎正在将其数据库从SQL Server 2005升级到SQL Server 2005上的“新”数据库，因此上述策略可能不是最优的。

#2

I don't believe there is one command to do this.

我不相信有一个命令可以做到这一点。

However you should be able to write a query which does this, using 1 or 2 cursors and a query like:

但是，您应该能够编写这样的查询，使用1或2游标和以下查询:

select t.name As TableName, c.Column_Name
from sys.tables t
INNER JOIN information_schema.columns c ON c.Table_Name = t.Name
ORDER BY t.name

This should return all table and columns in your database.

这将返回数据库中的所有表和列。

Then use:

然后使用:

sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

To rename each column.

将每一列。

#3

Short answer - no.

简短的回答,没有。

If you need to do this (and many studies suggest that all upper case names detract from readability), you'll have to generate new tables with these upper case names, copy the data from the old to the new table, drop the old tables, rename the new tables, and re-establish all of the foreign key relationships.

如果你需要这样做(和许多研究表明,所有大写名称减损可读性),您需要生成新表与这些大写的名字,复制数据从旧到新表中,删除旧表,重命名的新表,并重建所有的外键关系。

Is there a good reason to do this?

有充分的理由这样做吗?

#1