FETCH with Table Variable?

Question

Post reply

FETCH with Table Variable?

Steve Dingle

SSChasing Mays

Points: 601
More actions
January 14, 2007 at 9:00 am

#103301

Heya All,
If I can't use FETCH no need to read further if I can...
..for my first trick in using table variables I wanted to store the list of tables in a db , then move through it printing each table. I know there are probably better ways to perform this as I am implying I'm trying to learn about table variables.
I keep getting a message that I must DECLARE @DBTables from the code below. Am I missing something obvious.
DECLARE@TableCount int, @Table_Name nvarchar(128)
SET @TableCount= 0
SET @Table_Name= ''
-- Get a list of the tables
DECLARE @DBTables TABLE ( TABLE_NAME nvarchar(128) )
INSERT INTO @DBTables SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
-- Verify we have at least 1 table
SET @TableCount = @@ROWCOUNT
IF @TableCount > 0 BEGIN
FETCH NEXT FROM @DBTables
-- Move through the list of tables
WHILE (@@FETCH_STATUS = 0) BEGIN
PRINT @Table_Name
Much Thanks,
Steve Dingle

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Why not use SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

ORDER BY TABLE_NAME

as the source of the trigger?

also you might want to check : EXEC SP_MsForEachTable 'PRINT ''?'''

Steve Dingle SSChasing Mays Points: 601 More actions · Answer 2

Heya Ninja

Thanks for your response, as I had mentioned, there are probably better ways to do what I want but I am trying to get the hang of what I can/can't do with table variables. So this is a learning experience for me on those (table variables). None of the examples i have come across show a way to move through them.

FWIW, my end result is to create a SP which will be creating dynamic SQL statements based on table namd and fields and processing them... but I'm taking babay steps 🙂

So, do you know if I can use FETCH with a table variable to loop/move through contents of that table variable? Or is there another prefered method

Much Thanks,

Steve Dingle

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Exemple : Select count(*) FROM ALL tables. I suggest you run this on a small database like pubs or northwind...

CREATE TABLE #Results (TableName VARCHAR(128), Total INT)

INSERT INTO #Results (TableName, Total)

EXEC SP_MsForEachTABLE 'SELECT ''?'' AS TableName, COUNT(*) AS Total FROM ?'

SELECT TableName, Total FROM #Results ORDER BY Total DESC, TableName

DROP TABLE #Results

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

And for using table name you'd have to use a cursor like so :

DECLARE authors_cursor CURSOR   FOR SELECT * FROM @TableVarOPEN authors_cursorFETCH NEXT FROM authors_cursor

BTW You'll then have to use dynamic sql to do anything with the table.

Steve Dingle SSChasing Mays Points: 601 More actions · Answer 5

Hello Ninja

Thanks again for your response. Since you are using a temp table in one and a cursor in another, I'll take it to mean I cannot use a table variable with FETCH.

Much Thanks,

Steve Dingle

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

You can but it must be in the source of the cursor. The fetch command works only with the cursor... nothing else.

Steve Dingle SSChasing Mays Points: 601 More actions · Answer 7

Thanks again. FWIW, the reason I kept on about Table variable was because most stuff I was reading said to use them as your first choice if you could, then temp tables, then cursors

Much Thanks,

Steve Dingle

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

It's good to keep that in mind... However table variables and temp tables are often interchangable.