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;

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'''  

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/

---

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

---

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

---

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