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:
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