January 14, 2007 at 9:00 am
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
Steve Dingle
January 14, 2007 at 9:58 am
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 ''?'''
January 14, 2007 at 10:18 am
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
Steve Dingle
January 14, 2007 at 10:39 am
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
January 14, 2007 at 10:44 am
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_cursorBTW You'll then have to use dynamic sql to do anything with the table.January 14, 2007 at 10:52 am
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.
Steve Dingle
January 14, 2007 at 10:56 am
You can but it must be in the source of the cursor. The fetch command works only with the cursor... nothing else.
January 14, 2007 at 11:10 am
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
Steve Dingle
January 14, 2007 at 11:39 am
It's good to keep that in mind... However table variables and temp tables are often interchangable.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply