Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Changing a column name in multiple tables

Author  Topic 

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2013-11-26 : 14:30:25
I need to change the name of a flag column in about 1300 tables. I've come up with the following code:



DECLARE
@TableName AS NVARCHAR(255)
,@TableSchema AS NVARCHAR(255)
,@SQLStatement AS NVARCHAR(MAX)


DECLARE c_tables CURSOR FOR
SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM [INFORMATION_SCHEMA].[COLUMNS] AS c
JOIN [INFORMATION_SCHEMA].[TABLES] AS t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.[COLUMN_NAME] = 'DW_DeletedFlag'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME

OPEN c_tables
FETCH NEXT FROM c_tables INTO @TableSchema, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN --WHILE

SET @SQLStatement = 'sp_RENAME ' + '''' + @TableSchema + '.' + @TableName + '.DW_DeletedFlag' + ''', ''DW_WDSRecordNumberDeleted'', ''COLUMN''';
EXECUTE sp_ExecuteSQL @SQLStatement;

FETCH NEXT FROM c_tables INTO @TableSchema, @TableName
END --WHILE

CLOSE c_tables;
DEALLOCATE c_tables;


Is there a way to do this without cursors?

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-26 : 14:45:16
Why do you need to do this without a cursor? Sure there are other solutions, but you still must loop to achieve this. There is no cursor overhead for something like this.

If you want an alternative, use a WHILE loop. But there is no benefit.


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 04:54:36
If you want to avoid explicit use of loop you can use this


EXEC sp_msforeachtable ' IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE ''['' + TABLE_SCHEMA+ ''].[''+ TABLE_NAME + '']''= ''?'' AND COLUMN_NAME = ''DW_DeletedFlag'') EXEC sp_rename ''?'' + ''.DW_DeletedFlag'',''DW_WDSRecordNumberDeleted'', ''COLUMN'''


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-27 : 13:02:50
But it's still looping, so it's the same thing. Looping is not an issue for things like this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-28 : 06:11:28
quote:
Originally posted by tkizer

But it's still looping, so it's the same thing. Looping is not an issue for things like this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/


yep true
Thats why I specified "If you want to avoid explicit use of loop"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-11-29 : 19:55:31
quote:
Originally posted by visakh16

quote:
Originally posted by tkizer

But it's still looping, so it's the same thing. Looping is not an issue for things like this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/


yep true
Thats why I specified "If you want to avoid explicit use of loop"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Sorry... that doesn't avoid explicit looping at all. It just obfuscates the fact that you're using a loop on steroids. The stored procedure still has explicit looping in it.

I agree though... it's not a problem in cases such as this. I just don't want anyone to think that stored procedure avoids loops in any way, shape, or form.

--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."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 02:07:39
quote:
Originally posted by Jeff Moden

quote:
Originally posted by visakh16

quote:
Originally posted by tkizer

But it's still looping, so it's the same thing. Looping is not an issue for things like this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/


yep true
Thats why I specified "If you want to avoid explicit use of loop"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Sorry... that doesn't avoid explicit looping at all. It just obfuscates the fact that you're using a loop on steroids. The stored procedure still has explicit looping in it.

I agree though... it's not a problem in cases such as this. I just don't want anyone to think that stored procedure avoids loops in any way, shape, or form.

--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."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."


Thats exactly what I meant
From OPs request the idea I got is that OP is looking at a solution which doesnt have cursors or WHILE loops which is why i suggested this. And I know it uses looping on the background

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-11-30 : 13:05:30
Look at the code for that proc. Tell me it doesn't use cursors or while loops. ;-)

The real key here is this is the perfect use for a cursor or while loop.

--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."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-03 : 11:19:58
In these situations I prefer to generate a SQL Script, which I can eyeball and check, and then run that.

SELECT 'EXEC sp_rename ' + '''' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + '.' + c.COLUMN_NAME + ''', ''DW_WDSRecordNumberDeleted'', ''COLUMN'''
FROM [INFORMATION_SCHEMA].[COLUMNS] AS c
JOIN [INFORMATION_SCHEMA].[TABLES] AS t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.[COLUMN_NAME] = 'DW_DeletedFlag'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME

Running them blind, by executing them with EXEC (@strSQL) or sp_ExecuteSQL, has a habit of doing something that I have not anticipated ...
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-03 : 11:21:23
P.S. Not sure if it applies in this situation, but INFORMATION_SCHEMA can exclude objects that the user doesn't have the right sort of permissions to be able to see, or perhaps even just to enquire on. Can't remember the exact circumstances, only that it has come up before and caught folk out :(
Go to Top of Page
   

- Advertisement -