Cursor among several databases

[valigula]

Hi all,
I am wondering if anyone can help.

I am doing a sp that search for any string in any row of any table in
any database. The idea is to create the sp on the master database and
from there call it passing as parameter the database to do the search
and the string to be serached.

I using cursor (two cursors, one inside the other)to get this . My
question is is it possible to pass as a parameter the dbname to be use
in the cursor later something similar to this:

DECLARE cursor_tables CURSOR
FOR 'SELECT TABLE_NAME
FROM '+@dbname+'.INF ORMATION_SCHEMA .TABLES
WHERE TABLE_TYPE = ''BASE TABLE' '

Thanks in advance

[valigula]

Re: Cursor among several databases

On 4 jul, 11:05, valigula <valig...@gmail .comwrote:

Hi all,
I am wondering if anyone can help.
>
I am doing a sp that search for any string in any row of any table in
any database. The idea is to create the sp on the master database and
from there call it passing as parameter the database to do the search
and the string to be serached.
>
I using cursor (two cursors, one inside the other)to get this . My
question is is it possible to pass as a parameter the dbname to be use
in the cursor later something similar to this:
>
DECLARE cursor_tables CURSOR
FOR 'SELECT TABLE_NAME
FROM '+@dbname+'.INF ORMATION_SCHEMA .TABLES
WHERE TABLE_TYPE = ''BASE TABLE' '
>
Thanks in advance

researching a bit more finally found the solution ...

set @sqltables = 'DECLARE cursor_tables CURSOR FOR
SELECT TABLE_NAME
FROM '+@dbname+'.INF ORMATION_SCHEMA .TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' '
exec (@sqltables)



open cursor_tables