Nested Cursors

Morning everyone,

I have a sp that I've created that is to show me everyone table name and column name using nested cursors. However when I execute the procedure it doesn't show me the names, it just tells me the command completed successfully. Here is the code:

CREATE PROCEDURE uspSeeAllViews
AS
SET NOCOUNT ON
DECLARE @.strMessage VARCHAR(100)
DECLARE @.strColumn VARCHAR(100)
DECLARE @.strView VARCHAR(100)
DECLARE @.strCommand VARCHAR(250)

DECLARE crsViews CURSOR FOR

SELECT
name AS strView
FROM
sysobjects
WHERE
type = 'U'

OPEN crsViews
FETCH NEXT FROM crsViews INTO @.strView
WHILE @.@.FETCH_STATUS = 0 BEGIN

DECLARE crsColumns CURSOR FOR

SELECT
name AS strColumn
FROM
syscolumns
WHERE
name = @.strView

OPEN crsColumns
FETCH NEXT FROM crsColumns INTO @.strColumn
WHILE @.@.FETCH_STATUS = 0 BEGIN

PRINT @.strView + ':' + @.strColumn
FETCH NEXT FROM crsColumns INTO @.strColumn
END

CLOSE crsColumns
DEALLOCATE crsColumns

FETCH NEXT FROM crsViews INTO @.strView
END

CLOSE crsViews
DEALLOCATE crsViews

Thanks for looking, any ideas??Instead of
FETCH NEXT FROM crsViews INTO @.strView

try
FETCH crsViews INTO @.strView

I haven't used NEXT FROM before, and i think you might be skipping records.|||If you are in "grid" mode within Query Analyzer, you might want to check the messages tab.

Just FYI, you can combine both cursors into a single SELECT for much better performance and simplicity too.

-PatP|||Think of nesting cursors as the database equivalent of shoving a cigarette into a cigar. It ain't healthy.|||Why would you do this anyway??? I must be totally missing the boat on this one.

1. You created two cursors, which are horrible on processor efficiency to give you something that SQL Server already does for you with the

2. SELECT TABLE_NAME + ':' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
--What's the difference here?

If you ever do use cursors, which you should never do, you should have an overriding reason to do so.|||Never is a pretty strong word. I have five or six occasions when cursors were required...in the last ten years!

Cursors are a vb programmer's way of saying "Kilroy was here".|||Yeah, but when you have to take a column and execute its contents, then cursors are really nice. Other than that, I don't really have much use for them.

-PatP|||Yup. I've had to do that...about five or six times...

The one good thing about finding cursors in a client's code is that you can guarantee them that what you are developing will run faster than what they had. The assignment I'm on now is advising a client on moving OLAP onto a SQL Server platform. Their current system is based on DB2 and runs cobol code that creates and executes dynamic SQL loaded with cursor declarations.

Yeah, I think this is gonna be faster...