0

I've got a SQL Server database running in SSMS which contains ~75 tables. I've just realized every table has a modfiedBy column header, which is misspelled and should instead be spelled modifiedBy.

Is there a way to change (ALTER??) all these column headers programatially via SQL, so that I don't have to right click --> design and manually respell it on every single table? Could anyone provide an example that may work?

Kyle Vassella
  • 1,329
  • 4
  • 20
  • 43
  • @Lamu I can't be 100% sure if it's the same on every table, but I believe most if not all of them will be `varchar(50)`. (there may be one or two in there w/ `nvarchar(50)` but I can change these myself. – Kyle Vassella Nov 30 '18 at 22:34
  • Don't worry i was havinga mental blipp anyway. You'll need dynamic sql. Example on the way. – Larnu Nov 30 '18 at 22:35
  • Are any of those columns referenced in keys, stored procedures, views or whatever? What version of SQL? – Shawn Nov 30 '18 at 22:39

1 Answers1

1

You'll need a dynamic SQL solution for this. This should do the job; provided it doesn't break any Constraints, keys. It also won't update any objects referencing the old name.

USE YourDatabase;
GO

DECLARE @SQL nvarchar(MAX);

SET @SQL = STUFF((SELECT NCHAR(10) +
                         N'EXEC sp_rename ' + QUOTENAME(s.[name] + N'.' + t.[name] + N'.' + c.[name],N'''') + N',''modifiedBy'',''COLUMN'';'
                  FROM sys.schemas s
                       JOIN sys.tables t ON s.schema_id = t.schema_id
                       JOIN sys.columns c ON t.object_id = c.object_id
                  WHERE c.[name] = N'modfiedBy'
                  FOR XML PATH(N'')),1,1,N'');
PRINT @SQL;
EXEC sp_executesql @SQL;
Larnu
  • 61,056
  • 10
  • 27
  • 50