DYNAMIC USE

I would need go along the current sql server using a cursor (another
alternatives will be welcomed) and changing of database
Something like that:
DECLARE @.BD AS CHAR(20)
declare cursorbd cursor fast_forward for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status = 0
begin
USE @.BD
GO
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
fetch next from cursorBD into @.BD
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
But it isn't working.
Regards,The GO command signals the end of a batch of T-SQL statements. You put yours
in the middle of what you wanted to be the batch, splitting it in two.
But that's not the real problem. The trouble starts with you considering
using a cursor. You don't need to.
I could very well guess what you're trying to do, but I'd instead prefer you
to tell us.
So, here it goes:
What are you trying to do? Display a list of table names for all your
databases?
ML
p.s. if you answer 'yes' to my last question, we're half way there. :)|||DECLARE @.BD varCHAR(100)
declare @.string nvarchar(100)
set @.bd=3D''
declare cursorbd cursor for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT
IN('MASTER','PUBS','MODEL','TE=ADMPDB','
MSDB','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status =3D 0
begin
set @.string =3D N' USE '+@.BD+''
--print @.string
exec sp_executesql @.string
SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES
fetch next from cursorBD into @.BD
END=20
CLOSE CURSORBD=20
DEALLOCATE CURSORBD|||Hi
You shoudl read http://www.sommarskog.se/dynamic_sql.html on issues
regarding dynamic SQL. For you query use three part naming instead of the US
E
statement
declare cursorbd cursor fast_forward for
SELECT 'SELECT TABLE_NAME FROM ' + QUOTENAME(NAME) +
'.INFORMATION_SCHEMA.TABLES' FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
declare @.sqlstmt nvarchar(4000)
open cursorbd
fetch next from cursorBD into @.sqlstmt
while @.@.fetch_status = 0
begin
exec (@.sqlstmt)
fetch next from cursorBD into @.sqlstmt
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
John
"Enric" wrote:

> I would need go along the current sql server using a cursor (another
> alternatives will be welcomed) and changing of database
> Something like that:
> DECLARE @.BD AS CHAR(20)
> declare cursorbd cursor fast_forward for
> SELECT NAME FROM MASTER.DBO.SYSDATABASES
> WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
> open cursorbd
> fetch next from cursorBD into @.BD
> while @.@.fetch_status = 0
> begin
> USE @.BD
> GO
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> fetch next from cursorBD into @.BD
> END
> CLOSE CURSORBD
> DEALLOCATE CURSORBD
> But it isn't working.
> Regards,|||Since this sounds like an administrative task, and not production code,
then you could use one of Microsoft's undocumented stored procedures to
help: sp_MSforeachdb
EXEC sp_MSforeachdb @.command1="SELECT TABLE_NAME as ? FROM
INFORMATION_SCHEMA.TABLES"
Of course, this will return all databases (including system and test),
and I would definitely NOT use this in production, as it may change in
future releases, yada, yada.
If you want to use a cursor, then you'll need to use dynamic SQL to
build your SQL statement:
DECLARE @.BD AS VARCHAR(20)
DECLARE @.SQL as varchar (200)
declare cursorbd cursor fast_forward for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status = 0
begin
SET @.SQL = 'USE ' + @.BD + '
SELECT TABLE_NAME AS ' + @.BD + '
FROM INFORMATION_SCHEMA.TABLES '
EXEC (@.SQL)
fetch next from cursorBD into @.BD
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
Finally, you could use SQL-DMO and a scripting language to do this
task; this option is the most powerful, because it allows you to treat
every SQL entity as an object, and expose their properties through an
evet-drive interface. I use this to script out my development
databases in order to check them into source control.
http://msdn.microsoft.com/library/d...br />
3tlx.asp
VBScript (abbreviated)
SET SQLServer = CreateObject("SQLDMO.SqlServer")
SET Database = CreateObject("SQLDMO.Database")
SET Table = CreateObject("SQLDMO.Table")
SET View = CreateObject("SQLDMO.View")
SET Proc = CreateObject("SQLDMO.StoredProcedure")
SET Func = CreateObject("SQLDMO.UserDefinedFunction")
SET Index = CreateObject("SQLDMO.Index")
SQLServer.LoginSecure = TRUE
SQLServer.Connect ServerName
For each Database in SQLServer.Databases
If Database.SystemObject = False Then
For Each Table In Database.Tables
'write out the tables to a file here
Next
End if
Next
HTH
Stu