Dynamic Field Name Using Cursor

Question

I'm trying to print the list of fields in each schema and table dynamically. since I need a print for further references not a table, I used a cursor to print them out, as follows:

DECLARE AutoFields CURSOR
FOR
    SELECT TABLE_SCHEMA,TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
OPEN AutoFields
DECLARE @TableSchema NVARCHAR(100)
DECLARE @TableName NVARCHAR(100)

FETCH NEXT
FROM AutoFields
INTO @TableSchema,@TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    EXECUTE('
            SELECT COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA=@TableSchema
            AND TABLE_NAME=@TableName
           ')
     FETCH NEXT
     FROM AutoFields
     INTO @TableSchema,@TableName
END 

CLOSE AutoFields
DEALLOCATE AutoFields

But i get this error over and over again:

Must declare the scalar variable "@TableSchema".

What am I missing here?

Pரதீப் · Accepted Answer · 2015-12-11 17:10:42Z

1

Why can't you just write

SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS

Also i think you need this in CURSOR

DECLARE AutoFields CURSOR
FOR
    SELECT TABLE_SCHEMA,TABLE_NAME
    FROM INFORMATION_SCHEMA.tables --Not Columns

answered Dec 11, 2015 at 17:10

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Miss.Alpha Over a year ago

because as i explained, i want the print of them for further usage, not a table with rows.

Pரதீப் Over a year ago

@Miss.Alpha - your execute('..') doing same thing as what my first query is doing. Not sure where you are printing it

Tab Alleman · Accepted Answer · 2015-12-11 17:10:40Z

1

You need to use the variables to concatenate your dynamic string, not be a part of it. Like this:

EXECUTE('
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA= ''' + @TableSchema + '''
        AND TABLE_NAME= ''' + @TableName + '''
       ')

answered Dec 11, 2015 at 17:10

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

2 Comments

Miss.Alpha Over a year ago

There's still a problem: Invalid column name 'x'. 'x' is the name of all the table names!

Tab Alleman Over a year ago

I'm not following you. What part of the code is this error occurring on? I don't see anywhere in the code you posted that it could happen.

Collectives™ on Stack Overflow

Dynamic Field Name Using Cursor

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related