CODEWITHSUNDEEP
sql-servert-sqlfor-loop
joshi123
joshi123

Reputation: 865

t-sql for loop to iterate through tables

I'm trying to execute the same sequence of actions on a list of tables, which are selected using a where clause. 

Select table_name 
From INFORMATION_SCHEMA.COLUMNS 
Where column_name = 'fieldA';

This generates a list of table names - these are the tables I want to handle with a for loop. Is there a way to iterate through this list of tables, something like this? 

for i in @tablelist:

    alter table i add new_col varchar(8);
    update i set new_col = old_col;

Upvotes: 5

Views: 16053

Answers (4)

Sean Lange
Sean Lange

Reputation: 33571

You do not need to use loops for this. You can use the system view to generate your dynamic sql for you a lot simpler (and faster) than loops.

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'alter table ['+ c.TABLE_NAME +'] add new_col varchar(8);
                update ['+ c.TABLE_NAME + '] set new_col = old_col;'
from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'fieldA'

select @SQL
--uncomment below when you are satisfied with the dynamic sql
--exec sp_executesql @SQL

Upvotes: 6

DBAMan
DBAMan

Reputation: 21

One way to accomplish this action would be to feed your list of tables you want to alter into a SQL cursor and iterate through the cursor to execute the ALTER table command on each table. The documentation on how to create a SQL cursor can be found here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql

Another way to accomplish this would be using a while loop in SQL with a count iterator. Information on this solution can be found here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql

Upvotes: 1

nejcs
nejcs

Reputation: 1262

For looping you either have to use cursors or while exists construct, where you remove entry from temporary table each time you process it.

As for passing table name to query, there is no way to do that without creating dynamic SQL query. You do that by creating query in string with concatenating table name into the right place. Use QUOTENAME function to perform quoting of name if there are any spaces or weird characters.

Example with cursor:

DECLARE @tables TABLE (
    Name SYSNAME
);
DECLARE @tableName SYSNAME;
DECLARE @sql NVARCHAR(4000);

INSERT INTO @tables
SELECT i.table_name
FROM INFORMATION_SCHEMA.COLUMNS i
WHERE i.column_name = 'fieldA';

DECLARE cur CURSOR LOCAL FAST_FORWARD
FOR
SELECT Name
FROM @tables;

OPEN cur;

FETCH NEXT FROM cur
INTO @tableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'ALTER TABLE ' + QUOTENAME(@tableName) + N' ADD new_col varchar(8);';
    SET @sql = @sql + N'UPDATE ' + QUOTENAME(@tableName) + N' SET new_col = old_col;';

    EXEC sp_executesql @sql;

    FETCH NEXT FROM cur
    INTO @tableName;    
END

CLOSE cur;
DEALLOCATE cur;

Upvotes: 2

LONG
LONG

Reputation: 4610

You could try this:

DECLARE @Table TABLE
(
TableName VARCHAR(50),
Id int identity(1,1)
)


INSERT INTO @Table
Select table_name From INFORMATION_SCHEMA.COLUMNS 
Where column_name = 'fieldA'

DECLARE @max int
DECLARE @SQL VARCHAR(MAX) 
DECLARE @TableName VARCHAR(50)
DECLARE @id int = 1

select @max = MAX(Id) from @Table


WHILE (@id <= @max)
BEGIN

SELECT @TableName = TableName FROM @Table WHERE Id = @id
SET @SQL =     'alter table '+ @TableName +' add new_col varchar(8);
                update '+ @TableName + ' set new_col = old_col;'
PRINT(@SQL)  --COMMENT THIS LINE OUT AND COMMENT IN THE NEXT EXEC(@SQL) IF YOU SEE THE CORRECT OUTPUT
--EXEC(@SQL)
SET @id = @id +1
END

Upvotes: 9

Related Questions