CODEWITHSUNDEEP
sqlsql-servert-sql
FNR
FNR

Reputation: 499

Iterate through all tables

I'm looking for a way to iterate through all tables in a database.

I've come up with this so far:

DECLARE @TableName VARCHAR(MAX)

DECLARE MyCursor CURSOR 
    FOR
        SELECT 
        DISTINCT TABLE_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_CATALOG ='MyDB' 
        AND TABLE_SCHEMA=N'dbo'

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT *
    FROM @TableName
    FETCH NEXT FROM MyCursor INTO @TableName
END 
CLOSE MyCursor
DEALLOCATE MyCursor

I get an error message in the While statement The @TableName tablevariable must be declared (translated from German).

I know there should be a variable of the type TABLE at FROM @TableName, but I haven't found how to do that, just how to declare new tables which I don't want.

Upvotes: 1

Views: 100

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37337

In order to execute select on all your tables, you have to use dynamic SQL, as mentioned in other answer. Additionally, you don't need cursor at all! :)

Try this:

declare @sql varchar(max) = '';

select @sql = @sql + 'select * from ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + ' ' 
from information_schema.tables

exec(@sql)

Upvotes: 3

Squirrel
Squirrel

Reputation: 24763

You can't SELECT * FROM @TableName

you will need to use Dynamic SQL

declare @sql nvarchar(max)

SELECT @sql = 'SELECT * FROM ' + QUOTENAME(@TableName)

exec sp_executesql @sql

Upvotes: 5

Related Questions