Professional Web Applications Themes

how to view sysobjects in all databases - Microsoft SQL / MS SQL Server

I need to get all the database's sysobjects tables in one result set (and tack on a database column, of course). Is this possible to achieve without resorting to using dynamic SQL? I can do it that way, but it's kind of a pain... TIA, Bob...

  1. #1

    Default how to view sysobjects in all databases

    I need to get all the database's sysobjects tables in one result set (and tack
    on a database column, of course). Is this possible to achieve without resorting
    to using dynamic SQL? I can do it that way, but it's kind of a pain...

    TIA,
    Bob

    Bob Guest

  2. #2

    Default Re: how to view sysobjects in all databases

    I'll take that as a no.

    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:uZjDCdXQDHA.1988TK2MSFTNGP12.phx.gbl...
    > you can use union all clause using 3 part naming convention,but rather than
    > querying system tables i would suggest you use information_schema views as
    > shown in following example. you can get more details about the available
    > information_schema views in the BOL
    > Ex:
    > select 'northwind' db_name,* from northwind..sysobjects
    > union all
    > select 'pubs' db_name,* from pubs..sysobjects
    >
    >
    > select * from pubs.information_schema.tables
    > union all
    > select * from northwind.information_schema.tables
    >
    > --
    > -Vishal
    > "Bob" <noemailnospam.net> wrote in message
    > news:el1MkYXQDHA.3700tk2msftngp13.phx.gbl...
    > > I need to get all the database's sysobjects tables in one result set (and
    > tack
    > > on a database column, of course). Is this possible to achieve without
    > resorting
    > > to using dynamic SQL? I can do it that way, but it's kind of a pain...
    > >
    > > TIA,
    > > Bob
    > >
    >
    >
    Bob Guest

  3. #3

    Default Re: how to view sysobjects in all databases

    You can take it in whatever way you want, but this is a 100%, YES!!!!!

    --
    Dean Savovic
    [url]www.teched.hr[/url]


    "Bob" <noemailnospam.net> wrote in message
    news:OuWSQ$YQDHA.1024TK2MSFTNGP12.phx.gbl...
    > I'll take that as a no.
    >
    > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > news:uZjDCdXQDHA.1988TK2MSFTNGP12.phx.gbl...
    > > you can use union all clause using 3 part naming convention,but rather
    than
    > > querying system tables i would suggest you use information_schema views
    as
    > > shown in following example. you can get more details about the available
    > > information_schema views in the BOL
    > > Ex:
    > > select 'northwind' db_name,* from northwind..sysobjects
    > > union all
    > > select 'pubs' db_name,* from pubs..sysobjects
    > >
    > >
    > > select * from pubs.information_schema.tables
    > > union all
    > > select * from northwind.information_schema.tables
    > >
    > > --
    > > -Vishal
    > > "Bob" <noemailnospam.net> wrote in message
    > > news:el1MkYXQDHA.3700tk2msftngp13.phx.gbl...
    > > > I need to get all the database's sysobjects tables in one result set
    (and
    > > tack
    > > > on a database column, of course). Is this possible to achieve without
    > > resorting
    > > > to using dynamic SQL? I can do it that way, but it's kind of a pain...
    > > >
    > > > TIA,
    > > > Bob
    > > >
    > >
    > >
    >

    Dean Savovic Guest

  4. #4

    Default Re: how to view sysobjects in all databases

    I quote my original question:

    "
    I need to get all the database's sysobjects tables in one result set (and tack
    on a database column, of course). Is this possible to achieve without resorting
    to using dynamic SQL? I can do it that way, but it's kind of a pain...
    "

    Exactly how does an answer with this as part of its solution

    "
    select * from pubs.information_schema.tables
    union all
    select * from northwind.information_schema.tables
    "

    answer my question with a 'YES'? In the general case, the SQL above must be
    generated dynamically, and I asked if there was a way to do this *without* using
    dynamic SQL. This reply completely ignores my question, doesn't provide any
    answer at all.

    Bob

    "Dean Savovic" <dsavovicinet.hr> wrote in message
    news:be3qam$jea$1brown.net4u.hr...
    > You can take it in whatever way you want, but this is a 100%, YES!!!!!
    >
    > --
    > Dean Savovic
    > [url]www.teched.hr[/url]
    >
    >
    > "Bob" <noemailnospam.net> wrote in message
    > news:OuWSQ$YQDHA.1024TK2MSFTNGP12.phx.gbl...
    > > I'll take that as a no.
    > >
    > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > news:uZjDCdXQDHA.1988TK2MSFTNGP12.phx.gbl...
    > > > you can use union all clause using 3 part naming convention,but rather
    > than
    > > > querying system tables i would suggest you use information_schema views
    > as
    > > > shown in following example. you can get more details about the available
    > > > information_schema views in the BOL
    > > > Ex:
    > > > select 'northwind' db_name,* from northwind..sysobjects
    > > > union all
    > > > select 'pubs' db_name,* from pubs..sysobjects
    > > >
    > > >
    > > > select * from pubs.information_schema.tables
    > > > union all
    > > > select * from northwind.information_schema.tables
    > > >
    > > > --
    > > > -Vishal
    > > > "Bob" <noemailnospam.net> wrote in message
    > > > news:el1MkYXQDHA.3700tk2msftngp13.phx.gbl...
    > > > > I need to get all the database's sysobjects tables in one result set
    > (and
    > > > tack
    > > > > on a database column, of course). Is this possible to achieve without
    > > > resorting
    > > > > to using dynamic SQL? I can do it that way, but it's kind of a pain...
    > > > >
    > > > > TIA,
    > > > > Bob
    > > > >
    > > >
    > > >
    > >
    >
    >
    Bob Guest

  5. #5

    Default Re: how to view sysobjects in all databases

    Bob (noemailnospam.net) writes:
    > I quote my original question:
    >
    > "
    > I need to get all the database's sysobjects tables in one result set
    > (and tack on a database column, of course). Is this possible to achieve
    > without resorting to using dynamic SQL? I can do it that way, but it's
    > kind of a pain...
    > "
    >
    > Exactly how does an answer with this as part of its solution
    >
    > "
    > select * from pubs.information_schema.tables
    > union all
    > select * from northwind.information_schema.tables
    > "
    >
    > answer my question with a 'YES'? In the general case, the SQL above must
    > be generated dynamically, and I asked if there was a way to do this
    > *without* using dynamic SQL. This reply completely ignores my question,
    > doesn't provide any answer at all.
    I don't know what Dean Savovic had in mind, but I can't seen any say
    to code a static statement that covers all databases. You would have
    to generate the SQL statement on the fly somewhere. Either down in T-SQL
    itself, or from some client program, which first retrieves the name of
    all databases.

    OK, it is not really true. This you can do:

    create table #temp (dbname sysname NOT NULL, objname sysname NOT NULL)
    go
    insert #temp (dbname, objname)
    EXEC sp_MSforeachdb [SELECT '?', name FROM sysobjects]
    go
    select * from #temp

    sp_MSforeachdb is unsupported and undoented, so use is at own risk.
    And, of course, if you look into the code, you'll find that it uses
    dynamic SQL...

    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

  6. #6

    Default Re: how to view sysobjects in all databases

    Interesting... but wow, no wonder it's not supported. A global cursor and magic
    numbers? I'll just stick with feeding a local cursor with sp_databases for now.
    But I will take the suggestion from sp_MSforeachdb to use DATABASEPROPERTYEX to
    only look at databases that have MULTI_USER UserAccess.

    Thanks,
    Bob

    "Erland Sommarskog" <sommaralgonet.se> wrote in message
    news:Xns93B28B22800EYazorman127.0.0.1...
    ....
    > OK, it is not really true. This you can do:
    >
    > create table #temp (dbname sysname NOT NULL, objname sysname NOT NULL)
    > go
    > insert #temp (dbname, objname)
    > EXEC sp_MSforeachdb [SELECT '?', name FROM sysobjects]
    > go
    > select * from #temp
    >
    > sp_MSforeachdb is unsupported and undoented, so use is at own risk.
    > And, of course, if you look into the code, you'll find that it uses
    > dynamic SQL...
    Bob Guest

  7. #7

    Default Re: how to view sysobjects in all databases

    Bob (noemailnospam.net) writes:
    > Interesting... but wow, no wonder it's not supported. A global cursor
    > and magic numbers?
    All these sp_MSx are undoented and unsupported(*). They are written
    to serve some other tool like Enteprise Manager, DMO or similar.

    With the exception one single procedure which has made it to Books Online.
    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

Similar Threads

  1. DW Split View - Design View
    By kscap in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: May 1st, 06:00 PM
  2. View Uncataloged Databases
    By AJS in forum IBM DB2
    Replies: 3
    Last Post: September 12th, 01:44 PM
  3. Federated Databases, joins across databases etc
    By Benjamin Stewart in forum IBM DB2
    Replies: 2
    Last Post: August 1st, 03:05 PM
  4. Standard View vs. Layout View
    By Joe {RoastHorse} in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 23rd, 04:03 PM
  5. Wierd error when going to Design View from HTML view
    By VB Programmer in forum ASP.NET General
    Replies: 1
    Last Post: July 10th, 03:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139