CODEWITHSUNDEEP
sqlsql-serverssmssql-server-2019
Iman Estiri
Iman Estiri

Reputation: 76

Writing a query to generate some scripts to rename some columns in entire database

I have a SQL Server 2019 database (a big one). This database has about 3000 columns and about 200 tables and for each table we have at least 3 stored procedures.

Anyway we have some columns in this database that start with lower case letters, like userID. They should be UserID. My boss said that we should rename all these columns. I used a Select statement to find these columns.

Here is the code :

select * from information_schema.columns where ascii(left(column_name, 1)) between ascii('a') and ascii('z');

So I have The Column Name , Table Name And Also Schema Name . I want a Query For Generate Some Scripts To Rename These Columns That Start With Upper Case Letter in Entire Database .(Procedures , Tables , Views and .....)

The query Should Generate Scripts Like this :

Alter [sch].[tableName] Alter Column ColumnName INT

If There Is A Better Way Let me Know .

i want The Query or Procedure to Do This .

Thank You So Much ...

Upvotes: 0

Views: 425

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Although renaming the table column is not difficult using sp_rename, changing the referencing views and stored procedures is non-trivial without the help of tools.

The T-SQL below will generate script to rename all columns that start with a lower case letter. However, it is fragile in that renames will fail when enforced dependencies exist (e.g. schema bound objects) and will not handle views, procs. et. al.

SELECT 
      N'EXEC sp_rename ''' 
    + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'.' + QUOTENAME(c.name) 
    + ''', ''' + UPPER(LEFT(c.name, 1)) + SUBSTRING(c.name,2,127) + ''', ''COLUMN'';'
FROM sys.schemas AS s
JOIN sys.tables AS t ON t.schema_id = s.schema_id
JOIN sys.columns AS c ON c.object_id = t.object_id
WHERE 
    LEFT(c.name, 1) COLLATE Latin1_General_CS_AS <> UPPER(LEFT(c.name, 1)) COLLATE Latin1_General_CS_AS
    AND t.is_ms_shipped = 0;

I suggest you use SSDT, which is included with Visual Studio 2019 (including the free Community Edition). Create a new SQL Server Database Project, import the existing database, rename the column using the Refactor-->Rename option, and then publish the project against the target database. Publish provides the option to apply the changes immediately and/or just generate the script. The script will include DDL for the column rename as will as change the column name in all referencing objects.

If the column is aliased with the undesired name in views, procs, etc., those aliases will also need to be changed.

Upvotes: 1

Related Questions