How do I list all tables in all databases in SQL Server in a single result set?
Posted
by msorens
on Stack Overflow
See other posts from Stack Overflow
or by msorens
Published on 2010-05-20T16:23:21Z
Indexed on
2010/05/20
16:30 UTC
Read the original article
Hit count: 246
sql-server
|metadata
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:
sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'
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.
© Stack Overflow or respective owner