Adding variable to SELECT for CURSOR

[kevin.luke@ecoa]

I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable.
The following fails to parse:

Declare Cursor1 Cursor
For
'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name'
Open cursor1

That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.

[MAK]

store it in variable

example

declare @x varchar(8000)

set @x = 'Declare Cursor1 Cursor For ' +
'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name'

exec (@x)

Open cursor1

[kevin.luke@ecoa]

That worked perfectly, Thank you.

[DLu]

MAK: you are the real expert.
How the idea of 'store declare xx cursor in variable' comes from? I just have the same puzzle like Kevin has, to loop the 30 around databases per server (around 80 servers) to defragment all the tables.
I have made the cursors for database and then table and then index with this wonderful idea.

thanks
David

[MAK]

use this undocumented procedure

sp_msforeachtable

[MAK]

sp_msforeachdb

why cant you use Maintenance plan?

[DLu]

To certain degree, I feel more control with the code and jobs I created. ex. one server with 700 databases, I could not run at batch, instead, need to run different time with different set of the databases with passing parameters to it.

just by curiousity, is there any undoc proc for sp_msforeachsvr? (just kidding, otherwise I could run cross the 50 servers...)
regards
David

[MAK]

FYI. both msforeachdb and msforeachtable cannot be used together.

refer my posting reg this

http://forums.databasejournal.com/sh...=spmsforeachdb