Results 1 to 4 of 4

Thread: nesting cursors

  1. #1
    Join Date
    May 2004
    Location
    Ohio
    Posts
    61

    Unanswered: nesting cursors

    Hi, i have to nest 2 cursors together and frankly i'm lost.

    Here's what I have so far

    CREATE PROCEDURE SeeAllColumns
    AS
    SET NOCOUNT ON
    DECLARE @strMessage VARCHAR(100)
    DECLARE @strColumn VARCHAR(100)
    DECLARE @strTable VARCHAR(100)
    DECLARE @strCommand VARCHAR(250)

    SELECT @strMessage = 'SELECT ALL TABLES'
    PRINT @strMessage
    DECLARE crsTables CURSOR FOR
    SELECT
    name AS strTable
    FROM
    sysobjects
    WHERE
    name LIKE 'T%'

    OPEN crsTables
    FETCH NEXT FROM crsTables INTO @strTable
    WHILE (@@FETCH_STATUS = 0) BEGIN

    SELECT @strMessage = 'SELECT ALL COLUMNS'
    PRINT @strMessage

    SELECT @strCommand = ' SELECT ' + @strColumn + ' FROM ' + @strTable

    EXECUTE (@strCommand)

    FETCH NEXT FROM crsTables INTO @strTable

    END

    DEALLOCATE crsTables


    PRINT 'DONE'

    What i need to do is select all columns from all tables, but none of the actual data within the columns, just the names. I just don't know where or how to insert the 2nd cursor.
    Can someone help me?

    Thanks!
    Last edited by Smitty81; 05-20-04 at 11:27.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Frankly ... you lost me somewhere in the middle of the proc ... could you plz explain what you are trying to do here ...
    as far as it seems to me .. this proc will generate as many resultsets as the total no of columns in the database
    Code:
    DECLARE crsTables CURSOR FOR
    SELECT
    name AS strTable
    FROM
    sysobjects
    WHERE 
    name LIKE 'T%'
    should probably be
    Code:
    DECLARE crsTables CURSOR FOR
    SELECT
    name AS strTable
    FROM
    sysobjects
    WHERE 
    xtype = 'U'
    if you are trying to select all user tables
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2003
    Posts
    94
    Code:
    declare @tbl sysname
    declare @clm sysname
    
    declare tbls cursor local fast_forward for
    	select [TABLE_NAME]
    		from [INFORMATION_SCHEMA].[TABLES]
    		where [TABLE_TYPE] = 'BASE TABLE'
    
    open tbls
    
    fetch next from tbls into @tbl
    while @@fetch_status = 0
    begin
    	declare cols cursor local fast_forward for
    		select [COLUMN_NAME]
    			from [INFORMATION_SCHEMA].[COLUMNS]
    			where [TABLE_NAME] = @tbl
    
    	open cols
    
    	fetch next from cols into @clm
    	while @@fetch_status = 0
    	begin
    		print @tbl + ':' + @clm
    
    		fetch next from cols into @clm
    	end
    
    	close cols
    	deallocate cols
    	
    	fetch next from tbls into @tbl
    end
    
    close tbls
    deallocate tbls

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    you can just get by with 1 cursor if you declare it against this statement (alternatively you can use information_schema.columns view):

    select cast(object_name(id) as char(128)), colid, [name] from syscolumns
    where objectproperty(id, 'istable')=1 and objectproperty(id, 'ismsshipped')=0
    order by id, colid

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •