For JohnSQL, CDUN2 and Andrew Deren

Question

For JohnSQL, CDUN2 and Andrew Deren

RLSQL

Say Hey Kid

Points: 713
More actions
May 19, 2006 at 3:03 pm

#168205

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 1

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(

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)