Post reply

sp_msforeachdb

  • October 21, 2004 at 3:21 pm

    #161755

    Hello All,

    Does anyone know how to make sp_msforeachdb work with database name starting with number? It is not working in our case. The DB started with character is fine.

    Thank you for your help.

     

    David

  • October 22, 2004 at 2:27 am

    #527602

    Brows the Internet. There is an enourmous ammout of information about undocumented sp's.

    http://www.databasejournal.com/features/mssql/article.php/1490641

    Regards,

    Gkramer

  • October 22, 2004 at 2:43 am

    #527606

    It runs fine on a SQL Server 7 Standard Edition. Haven't a SQL2K setup available right now. What release are you using?

    Greetz,
    Hans Brouwer

  • October 22, 2004 at 4:58 am

    #527617

    Did you try enclosing the dbname in square brackets?

    ie exec sp_msforeachdb "use [?]; select * from sysobjects"


    Shamless self promotion - read my blog http://sirsql.net

  • October 22, 2004 at 8:39 am

    #527664

    Dear All,

    Thanks all for your reply. Here is my script and I tried Nicholas's suggestion. Both of them are not working with those database name started with number.

    set quoted_identifier off

    go

    sp_msforeachdb @Command1="print '?' Select substring(name, 1, 30) TABLE_NAME

    from ?..sysobjects"

    Thanks

  • October 22, 2004 at 9:09 am

    #527671

    It looks like you're trying to get a list of sysobjects for every database. If so, copy and paste the following in Query Analyzer:

    sp_MSforeachdb 'select substring(name, 1, 30) ''[?]'' from [?]..sysobjects'

    This will list all the sysobjects with the database name as the column title. If you type it in, note that where you see '' (two single quotes looks like a double quote), it is actually two ' (single quotes). I noticed you're using double quotes.

  • October 22, 2004 at 9:54 am

    #527680

    Thanks Accent Care and Nicholas. It worked.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply