CODEWITHSUNDEEP
sql-servertable-rename
Richard
Richard

Reputation: 53

Renaming the column names in batch using SQL Server 2012

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.

Upvotes: 1

Views: 2022

Answers (1)

nejcs
nejcs

Reputation: 1262

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.

Upvotes: 2

Related Questions