CODEWITHSUNDEEP
sql-servert-sqlwhile-loop
astroluv
astroluv

Reputation: 793

How to use while loop to iterate through each table name in SQL Server?

I'm working with Dynamic SQL (still in the learning phase) and I'm stuck at a part where I need to use a WHILE loop:

  SET @tableName = (SELECT DISTINCT TableName FROM @dataStructure)

Here basically I want to make sure that the operations inside the while loop should occur for all the tables in the @tableName (defined above). I don't know how I can give this condition as an input for the while loop.

   WHILE()  #HOW CAN I PUT THE CONDITION HERE????
   BEGIN
       SET @str = ''
       SET @sqlstr = ''
    
       SELECT @table = TableName FROM @dataStructure
    
       SET @str = 'UPDATE a0' + char(13) + char(10)
                + ' SET a0.Mjolnir_Source_ID = CONCAT( ' 
    
    SELECT @str = @str + IIF(ReferenceTable  IS NULL, 'a0.' + columnName , alias + '.Mjolnir_Source_ID') + ',' 
                        FROM @dataStructure
                        WHERE TableName = @tableName AND ReferenceTable IS NOT NULL
                        ORDER BY columnName
    
    SELECT @str = @str + ') FROM ' + @table + ' a0'
    
    SELECT @sqlstr = @sqlstr +  +
                        + ' INNER JOIN ' + QUOTENAME(@U4SM_db_name) + '.dbo.' + QUOTENAME(ReferenceTable) + ' '  + alias + char(13) + char(10)
                        + ' ON a0.' + columnName + ' = ' + alias + '.' + ReferenceColumn + char(13) + char(10)
                        FROM @dataStructure 
                        WHERE TableName = @tableName AND ReferenceTable IS NOT NULL
                        ORDER BY columnPosition
    
    
    select @str + @sqlstr
    select @sqlstr
    SET @tableName = @tableName + 1
 END

Can anyone please help me out here?

Upvotes: 0

Views: 5936

Answers (3)

Nick Fotopoulos
Nick Fotopoulos

Reputation: 579

Don't recreate the wheel unless you need a better wheel:

sp_MSforeachtable

https://www.sqlshack.com/an-introduction-to-sp_msforeachtable-run-commands-iteratively-through-all-tables-in-a-database/

If you are worried about using an undocumented procedure in production that might change in the future, simply script it out and create your own custom named version.

Upvotes: 0

seanb
seanb

Reputation: 6685

Here's an example of a WHILE loop. Basically, you get the first TableName, then if it's NOT NULL, you do your functions. Then get the next table name, and repeat as necessary.

DECLARE @CurrentTableName nvarchar(100)
DECLARE @CustomSQL nvarchar(4000)

SET @CurrentTableName = (SELECT TOP 1 TableName FROM @dataStructure ORDER BY TableName)

WHILE @CurrentTableName IS NOT NULL
BEGIN
    SET @CustomSQL = 'SELECT TOP 10 * FROM ' + @CurrentTableName
    EXEC (@CustomSQL)
    SET @CurrentTableName = (SELECT TOP 1 TableName FROM @dataStructure WHERE TableName > @CurrentTableName ORDER BY TableName)
END

Note that SQL commands often cannot contain variable names in key spots (e.g., SELECT * FROM @tableName). Instead, you save it as an SQL string (what I've called @CustomSQL above) and then EXEC it (put brackets around the variable name though).

Edit: Do this on a test site first before production, and know where the 'cancel query' button is. It's not often, but it's also not unknown, that the 'getting the next row' part isn't properly written and it just runs in a perpetual loop.

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

FETCH CURSOR with WHILE. Example:

DECLARE myCursor CURSOR FOR  
    SELECT DISTINCT TableName FROM @dataStructure;
OPEN myCursor;  
FETCH NEXT FROM myCursor INTO @table:Name;  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
      Print '   ' + @TableName 
      FETCH NEXT FROM myCursor INTO @TableName;  
   END;  
CLOSE myCursor;  
DEALLOCATE myCursor;  
GO

Upvotes: 0

Related Questions