samedi 27 décembre 2014

How to update all tables in a SQL Server database using a Loop

I have a common column in all my tables in the database, and I need to update the value of this column through a loop in all my tables


I try by this code but nothing happened



DECLARE TBL_CURSOR CURSOR
FOR ( SELECT Name FROM Sysobjects WHERE Type='U' )

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '';

DECLARE @TblName NVARCHAR(MAX);

OPEN TBL_CURSOR

FETCH NEXT FROM TBL_CURSOR INTO @TblName

BEGIN

SET @SQL = ' If not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = '+ @TblName +' and column_name = "CommonColumn") update ' + @TblName + ' set CommonColumn= 1 ';
FETCH NEXT FROM TBL_CURSOR INTO @TblName
END

CLOSE TBL_CURSOR
DEALLOCATE TBL_CURSOR

EXEC (@SQL);


Anyone have a better idea or fixing up this code?


Aucun commentaire:

Enregistrer un commentaire