Saturday 5 January 2008

Database Loop - WITHOUT CURSOR


SET NOCOUNT ON

DECLARE  @CurrentRow INT
DECLARE  @TotalRows INT

DECLARE  @DatabaseName NVARCHAR(255)                   
SELECT @CurrentRow = 1
               
DECLARE  @Databases  TABLE(
 UNIQUEROWID  INT   IDENTITY ( 1,1 )   PRIMARY KEY   NOT NULL
,DATABASENAME NVARCHAR(255)
)
   
INSERT INTO @Databases (DATABASENAME)
SELECT NAME
FROM SYS.DATABASES
WHERE DATABASE_ID > 4
               
SELECT @TotalRows = COUNT(* )
FROM @Databases
 
WHILE @CurrentRow <= @TotalRows  
BEGIN    
 SELECT @DatabaseName = DATABASENAME      
 FROM @Databases     
 WHERE UNIQUEROWID = @CurrentRow         
 PRINT @DatabaseName         
 SELECT @CurrentRow = @CurrentRow + 1  
END 

No comments: