SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Changing a column name in multiple tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stonebreaker
Yak Posting Veteran

USA
85 Posts

Posted - 11/26/2013 :  14:30:25  Show Profile  Reply with Quote
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

USA
36851 Posts

Posted - 11/26/2013 :  14:45:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

India
52317 Posts

Posted - 11/27/2013 :  04:54:36  Show Profile  Reply with Quote
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

USA
36851 Posts

Posted - 11/27/2013 :  13:02:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

India
52317 Posts

Posted - 11/28/2013 :  06:11:28  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 11/29/2013 :  19:55:31  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/30/2013 :  02:07:39  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 11/30/2013 :  13:05:30  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 12/03/2013 :  11:19:58  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 12/03/2013 :  11:21:23  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000