ProgrammingMSAccess.com

The ProgrammingMSAccess.COM Site

A Transact-sql script in a stored procedure that uses a server-side cursor enumerate the table names in the currently connected database.

Alter Procedure PrintTableNames

As

--Declare local variables; define cursor and a local variable

DECLARE @TableName varchar(255), @t varchar(255)

DECLARE TableCursor CURSOR FOR

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

SET @TableName = ''

--Make cursor available for use by opening it

OPEN TableCursor

--Fetch the first record from the cursor

FETCH NEXT FROM TableCursor INTO @t

--Loop through cursor records until none are left

--On each pass through loop, update a string with

--table names; display string

WHILE @@FETCH_STATUS = 0

BEGIN

SET @TableName= @TableName + ' ' + @t

FETCH NEXT FROM TableCursor INTO @t

END

SELECT @TableName as "Tables list"

--Clean up by releasing cursor resources

CLOSE TableCursor

DEALLOCATE TableCursor

Want to understand  SQL Server and Microsoft Access 2000 so that you can program it to do more tasks like this?  Get Professional SQL Server Development with Access 2000 by Rick Dobson from Wrox Press.  Learn more about the book by clicking here.

Copyright 2001 CAB, Inc. All rights reserved. Republication or redistribution of CAB, Inc. content, including by framing or similar means, is expressly prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be liable for any errors in the content, or for any actions taken in reliance thereon.