MS SQL Server - cursor How to >> use @dbname

Author

cursor How to >> use @dbname

WANNABE

2006-10-31, 12:15 am

The following query ERRORs out with this >>
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '@DBname'.

What can I do to get it to accept the cursor name to change DBs

SET NOCOUNT ON
DECLARE @DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @DBname
WHILE @@FETCH_STATUS = 0
BEGIN
use @DBname
select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
from sysobjects
where user_name(uid) !='DBO'
order by uid, name
fetch next from DB_Cursor
into @DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor

Thanks.

Barry

2006-10-31, 12:15 am

Try this...

SET NOCOUNT ON
DECLARE @DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @DBname
WHILE @@FETCH_STATUS = 0
BEGIN
declare @sql varchar(500)

Set @sql = 'use ' + @DBname +'
select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate

from sysobjects
where user_name(uid) !=''DBO''
order by uid, name'

Exec (@sql)

fetch next from DB_Cursor
into @DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor

Aaron Bertrand [SQL Server MVP]

2006-10-31, 12:15 am

You can't say USE @dbName because USE can't take a variable.

The following is undocumented and unsupported, so use at your own risk and
not in production code, but should work fine for occasional ad hoc usage:

EXEC sp_msForEachDB 'SELECT [Owner] = user_name(uid), [Table] = name, [Date]
= crdate
FROM ?.dbo.sysobjects
WHERE user_name(uid) != ''dbo''
ORDER BY uid, name'

You can do it in a more structured way using your own cursor instead of the
one in sp_msForEachDB, if this is a requirement, please post back.

"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eOL7zkF$GHA.4316@TK2MSFTNGP03.phx.gbl...
> The following query ERRORs out with this >>
> Server: Msg 170, Level 15, State 1, Line 14
> Line 14: Incorrect syntax near '@DBname'.
>
> What can I do to get it to accept the cursor name to change DBs
>
> SET NOCOUNT ON
> DECLARE @DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @DBname
> WHILE @@FETCH_STATUS = 0
> BEGIN
> use @DBname
> select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
> from sysobjects
> where user_name(uid) !='DBO'
> order by uid, name
> fetch next from DB_Cursor
> into @DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>
> Thanks.
>

WANNABE

2006-10-31, 12:15 am

Thanks Barry that looks great, but when I apply your changes I get this
error >>
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'DBO'.
and the error is displayed once for every DB on that server.
====================
===============
"Barry" <barry.oconnor@manx.net> wrote in message
news:1162237261.528489.238930@b28g2000cwb.googlegroups.com...
> Try this...
>
>
> SET NOCOUNT ON
> DECLARE @DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @DBname
> WHILE @@FETCH_STATUS = 0
> BEGIN
> declare @sql varchar(500)
>
> Set @sql = 'use ' + @DBname +'
> select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
>
> from sysobjects
> where user_name(uid) !=''DBO''
> order by uid, name'
>
> Exec (@sql)
>
> fetch next from DB_Cursor
> into @DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>

WANNABE

2006-10-31, 12:15 am

I just figured out tit to be a double quote issue, and I think I can find a
fix... Thanks
====================
===============
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:%23UFjjAG$GHA.3860@TK2MSFTNGP02.phx.gbl...
> Thanks Barry that looks great, but when I apply your changes I get this
> error >>
> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name 'DBO'.
> and the error is displayed once for every DB on that server.
> ====================
===============
> "Barry" <barry.oconnor@manx.net> wrote in message
> news:1162237261.528489.238930@b28g2000cwb.googlegroups.com...
>
>

Sponsored Links