I am looking for T-SQL code to list all tables in all databases in SQL Server (at least in SS2005 and SS2008; would be nice to also apply to SS2000). The catch, however, is that I would like a single result set . This precludes the otherwise excellent answer from Pinal Dave :
The above stored proc generates one result set per database . which is fine if you are in an IDE like SSMS that can display multiple result sets. However, I want a single result set because I want a query that is essentially a “find” tool: if I add a clause like WHERE tablename like ‘%accounts’ then it would tell me where to find my BillAccounts, ClientAccounts, and VendorAccounts tables regardless of which database they reside in.
2010.05.20 Update, about 20 minutes later.
So far, Remus’ answer looks most interesting. Rather than post this as an answer and award it to myself, I am posting a version of it here that I have modified to include the DB name and a sample filter clause. It is looking like Remus will get the credit for the answer, though, at this point!
2010.05.24 Update — New Front runner!
The feedback and answers have been great. Continued collaborative participation has led to a new frontrunner. KM’s answer from May 21!
Here are the issues I uncovered with Remus’ solution:
Major issue: Users have different permissions which leads the query to succeed based on the data (i.e. the filtering value). Run on my production database with no filtering (i.e. omitting the WHERE clause) I received this error on several DBs that I do not have permission to access:
The server principal “msorens” is not able to access the database “ETLprocDB” under the current security context.
The query will succeed with some filtering clauses–those that do not touch the DBs outside my access level.
Minor issue: Not easily degradable to SQL Server 2000 support (yes, there are still some of us out there using it. ) because it builds a single string while accumulating entries for each database. With my system, I surpassed the 8000-character mark at around 40 databases.
Minor issue: Duplicate code–the loop setup essentially duplicates the loop body. I understand the rationale but it is just a pet peeve of mine.
KM’s answer is not afflicted by these issues. The stored proc sp_msforeachdb takes into account the user’s permissions so it avoids permission problems. I have not yet tried the code with SS2000 but KM indicates the adjustments that should do it.
I am posting next my modifications to KM’s answer based on my personal preferences. Specifically:
- I have removed the server name as it does not really add anything in the result set.
- I have split the name components into their own fields in the result set (db name, schema name, and table name).
- I have introduced separate filters for each of the three fields.
- I have added sorting by the three fields (which can be modified to your preferences).
Here is my modification to KM’s code (with a sample filter applied just to the table name):
asked May 20 ’10 at 16:23