Renaming the column names in batch using SQL Server 2012

Question

I found this useful query to rename all my tables, indexes and constrains but I just figured out it didn't rename the columns.

SELECT 'exec sp_rename ' + '''' + NAME + '''' + ', ' + '''' + replace(NAME, 'Tb', 'Tabela') + ''''
FROM sysObjects
WHERE
NAME LIKE 'Tb%'

I know there's syscolumns but I'm not sure how to use in this case.

Question: How can I get the same result of this query but for columns instead of tables?

I appreciate your help in this. I'm using SQL Server 2012. Thanks.

I mean, you are just selecting data, which in this case should give you every query you need to actually run. You'll have to copy the results, paste them in a window and then run it — Lamak, Aug 16 '17 at 16:37
The question is... how I get the same result of this query but for columns instead of tables. — Richard, Aug 16 '17 at 16:48
You mean something like this: https://stackoverflow.com/a/174586/5894241 — Nisarg Shah, Aug 16 '17 at 16:56

score 2 · Answer 1 · answered Aug 16 '17 at 17:10

2

You have to do a little more work:

SELECT 'exec sp_rename ' + '''' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + '.' + QUOTENAME(c.name) + '''' + ', ' + '''' + replace(c.name, 'Col', 'Column') + ''', ''COLUMN'''
FROM sys.columns c
    INNER JOIN sys.objects o ON c.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE c.name LIKE 'Col%'

Since you are renaming column, you must specify that third argument to sp_rename is COLUMN. You must also construct three part name in the form of [schema].[table name].[current column name] to point to the correct column.

answered Aug 16 '17 at 17:10

nejcs

1,212
10
14

Worked perfectly! Thank you very much, nejcs! – Richard Aug 16 '17 at 21:03
I'm glad that it worked. Please approve this answer and consider upvoting it. – nejcs Aug 17 '17 at 19:46

Renaming the column names in batch using SQL Server 2012

1 Answers1