How Update table columns name with replace and sp_rename in sql?

Question

I write this code but don't update my columns name

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @SQL =  N'EXEC sp_rename N' + t.[name] + N'.' + s.[name] + N',N' + Replace(s.[name],N'k',N'z') + N';'
            FROM     sys.columns s
            JOIN     sys.tables  t   ON s.object_id = t.object_id
            WHERE  t.name LIKE '%dbo.custommer%'

I don't have any exception and query is run

Answer 1

You can use a cursor to go through the columns and execute sp-rename on each one. You also need to use sys.schemas to check the schema name, as dbo is the schema, not part of the table name.

You also need QUOTENAME for correct escaping.

DECLARE @column nvarchar(1000), @newname sysname, @crsr CURSOR;

SET @crsr = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT
  CONCAT_WS(N'.', QUOTENAME(s.name), QUOTENAME(t.name), QUOTENAME(c.name)),
  REPLACE(c.name, N'k', N'z')
FROM sys.columns c
JOIN sys.tables t ON t.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = N'dbo'
  AND t.name = N'custommer';

OPEN @crsr;

WHILE 1=1
BEGIN
    FETCH @crsr INTO @column, @newname;
    IF @@FETCH_STATUS <> 0 BREAK;

    EXEC sp_rename @column, @newname, 'COLUMN';
END;

On a side note: if you have the same letter in all columns then you are probably doing something wrong in your database design.