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