For JohnSQL, CDUN2 and Andrew Deren
-
Hello Gentlemen,
The following code may be of interest to you:
Declare
crsColumnRenames Cursor
Local
Fast_Forward
Read_Only
For
Select
Table_Name
, Column_Name
From
Information_Schema.Columns
Where
Table_Name Like 'Imported%'
And CharIndex(' ', Column_Name) > 0
Order By
Table_Name, Column_Name
Declare
@TableName SysName
, @ColumnName SysName
, @ColumnNameRenamed SysName
, @RenameObject SysName
, @FetchRC Int
Open crsColumnRenames
Set @FetchRC = 0
While (@FetchRC = 0)
Begin
Fetch Next From crsColumnRenames Into @TableName, @ColumnName
Set @FetchRC = @@Fetch_Status
If @FetchRC = 0
Begin
Set @ColumnNameRenamed = Replace(@ColumnName, ' ', '') -- Get rid of ' '
Set @ColumnNameRenamed = Replace(@ColumnNameRenamed, '/', '') -- Get rid of '/'
Set @ColumnNameRenamed = Replace(@ColumnNameRenamed, '(', '') -- Get rid of '('
Set @ColumnNameRenamed = Replace(@ColumnNameRenamed, ')', '') -- Get rid of ')'
-- RaisError('%s.%s is being renamed to %s', 0, 1, @TableName, @ColumnName, @ColumnNameRenamed)
Set @RenameObject = @TableName + '.' + @ColumnName
Exec sp_rename
@objname = @RenameObject
, @newname = @ColumnNameRenamed
, @objtype = 'Column'
End
End
Close crsColumnRenames
Deallocate crsColumnRenames
go
Richard
-
Nicely done. Obviously, we can't get away from a looping structure but this eliminates the cursor that so many dislike and has a couple of tricks in it that you may be interested in (including some documentation
).../********************************************************************
Purpose:
This script finds and repairs bad column names in tables whose name
begins with "Imported". A bad column name is one that contains any
of the following characters:
{space} / ( )
********************************************************************/
--===== If working table exists, drop it
IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
DROP TABLE #MyHead
--===== Declare local variables
DECLARE @Counter INT --General purpose counter
DECLARE @CountTo INT --Max for counter
DECLARE @OldObjectName SYSNAME -- tablename.columnname with bad name
DECLARE @NewObjectName SYSNAME -- tablename.columnname with good name
--===== Capture Table.BadColumnName and create Table.GoodColumnName
SELECT IDENTITY(INT,1,1) AS RowNum,
Table_Name + '.' + Column_Name AS OldObjectName,
Table_Name + '.'
+ REPLACE(
REPLACE(
REPLACE(
REPLACE(
Column_Name
,' ','')
,'/','')
,'(','')
,')','') AS NewObjectName
INTO #MyHead
FROM Information_Schema.Columns c
WHERE Table_Name = LIKE 'Imported%'
AND Column_Name LIKE '%[ ,/,(,)]%'
-- Capture the rowcount to control the renaming loop
SET @CountTo = @@ROWCOUNT
--===== Loop to rename the bad column names with the good ones
SET @Counter = 1
WHILE @Counter <= @CountTo
BEGIN
--===== Load the names into variable so can use with sp_ReName
SELECT @OldObjectName = OldObjectName,
@NewObjectName = NewObjectName
FROM #MyHead
WHERE RowNum = @Counter
--===== Rename the bad column
EXEC dbo.sp_ReName
@ObjName = @OldObjectName,
@NewName = @NewObjectName,
@ObjType = 'Column'
--===== Bump the counter
SET @Counter = @Counter + 1
END
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply