Prev: design issues with large amounts of data
Next: Release 1.3 of Mogwai ERDesignerNG available
From: valigula on 4 Jul 2008 05:05
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+'.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE' '

Thanks in advance
From: valigula on 4 Jul 2008 09:05
On 4 jul, 11:05, valigula <valig...(a)gmail.com> wrote:
> 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+'.INFORMATION_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+'.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' '
exec (@sqltables)



open cursor_tables
 | 
Pages: 1
Prev: design issues with large amounts of data
Next: Release 1.3 of Mogwai ERDesignerNG available