In this lesson we are going learn to use cursors to print a list of main tables in a database.

As always, I am using Adventure2012 as my database. If you do not have it, and you would like to play along, please refer to the following lessons to get up and running:

1. MS SQL Server: Installation
2. SQL: SELECT Statement

First let me show you where to go to find a list tables. SQL Server maintains a list of tables in a table called Information_Schema.Tables. Running the code below will give you results seen below.

Use AdventureWorks2012
go

SELECT * FROM INFORMATION_SCHEMA.TABLES
go

The last column, TABLE_TYPE lets you know what kind of table you are working with: BASE TABLE is the tables you generally refer to as Tables in your database. The other type  you will see in this database is VIEW.

Cursor

In SQL, cursors are a way of letting your step through a result set one row at a time. While the mere mention of cursors can cause many DBA’s and database programmers to twitch and convulse, I still think you should learn about them to have a well rounded SQL education. Also, cursors have come in handy for me more times than I can count.

Look at the code below:

declare @tableName nvarchar(255)

select @tableName = TABLE_NAME
from INFORMATION_SCHEMA.TABLES

print @tableName

First I declare a variable “@tableName” and set it with a select statement. I assign @tableName the values in column TABLE_NAME from INFORMATION_SCHEMA.TABLES.

However when I run print @tableName, you will see the variable only holds the last value. Each previous value was replaced by the one after it.

I want to print each value in the column. So we going to use a cursor

— indicates a REM comment

declare @tableName varchar(255)
declare myCursor CURSOR -- name your cursor
FOR                     -- set cursor to a result set
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'base table'
open myCursor                    -- get our cursor ready to use
Fetch next from myCursor into @tableName -- Fetch gets first  
                                         -- row in cursor results
While @@FETCH_STATUS = 0         --@@FETCH_STATUS is 0 while you still
                                 -- have rows in your cursor set. It changes
                                 -- to -1 when your cursor hits the final row
Begin                  -- Begin while loop
print @tableName       -- prints table name from row cursor is currently on
Fetch next from myCursor into @tableName  -- go to next row
end                   -- end While loop
close myCursor        -- close cursor for use
Deallocate myCursor   -- delete values from cursor