Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2009-05-11 : 11:47:00
|
| Is it possible to write a query that can identify all the tables in all DBs on the DB server (are there any system tables that identify what objects reside with which database instance)?For instance, on DB1, I can return a list of all objects on DB2 and DB3?ThanksHearty head pats |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-11 : 12:29:40
|
you can use thisEXEC sp_MSforeachdb 'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE''' |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-05-11 : 14:43:46
|
| Hey visakh16Thanks so much for the reply.Funnily enough, I saw this earlier, but kept getting 'procedure not found'. I realise now that I didn't write it correctly (in terms of capitals as my collation is in binary..DOH!)I do have a question though. I understand that this is an undocumented procedure. What does that mean exactly? That is is something created by the SQL Server developers for their own usage, and that it is not supported or guaranteed to exist in future versions?ThanksHearty head pats |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-05-11 : 22:21:19
|
quote:
Originally posted by Bex
Hey visakh16Thanks so much for the reply.Funnily enough, I saw this earlier, but kept getting 'procedure not found'. I realise now that I didn't write it correctly (in terms of capitals as my collation is in binary..DOH!)I do have a question though. I understand that this is an undocumented procedure. What does that mean exactly? That is is something created by the SQL Server developers for their own usage, and that it is not supported or guaranteed to exist in future versions?ThanksHearty head pats
Yes it won't work in future version. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-11 : 22:41:39
|
| Should be:EXEC sp_MSforeachdb 'SELECT * FROM [ ? ].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'''The other way might work, but you should explicitly include the database name via [ ? ] (remove spaces around ? in actual code) |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-05-12 : 02:19:16
|
THanks guys. Your help has been much appreciated!  Hearty head pats |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-05-12 : 05:10:38
|
quote:
Originally posted by Bex
Hey visakh16Thanks so much for the reply.Funnily enough, I saw this earlier, but kept getting 'procedure not found'. I realise now that I didn't write it correctly (in terms of capitals as my collation is in binary..DOH!)I do have a question though. I understand that this is an undocumented procedure. What does that mean exactly? That is is something created by the SQL Server developers for their own usage, and that it is not supported or guaranteed to exist in future versions?ThanksHearty head pats
Yes. It is not gauranteed that it will work in the next version of SQL Server. But you can simulate it usinghttp://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-05-12 : 13:40:29
|
| Perfect!!!! ThankYou!Hearty head pats |
|
|
|
|
|
|
|
|
Obtain list of all tables from a database