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','MSDB','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=''
declare cursorbd cursor for
        SELECT NAME FROM MASTER.DBO.SYSDATABASES
                WHERE NAME NOT
IN('MASTER','PUBS','MODEL','TE­MPDB','MSDB','NORTHWIND')


open cursorbd


fetch next from cursorBD into @BD


while @@fetch_status = 0
begin


set @string = N' USE '+@BD+''
--print @string
                      exec sp_executesql @string
                        SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES


                        fetch next from cursorBD into @BD


END
CLOSE CURSORBD
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 USE
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','MSDB','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

Show quoteHide quote

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','MSDB','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/default.asp?url=/library/en-us/sqldmo/dmoref_ob_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

Just Venting
Recyle an identity column number when max is reached
Nested sets in SQL - inventor?
Select Top @Variable * from table
raiserror in trigger