metadata - How do I list all tables in all databases in SQL Server in a single result set?

Question

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.

---

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!

declare @sql nvarchar(max);
set @sql = N'select b.name as "DB", a.name collate Latin1_General_CI_AI as "Table", object_id, schema_id, cast(1 as int) as database_id  from master.sys.tables a join sys.databases b on database_id=1 where a.name like ''account%''';

select @sql = @sql + N' union all select b.name as "DB", a.name collate Latin1_General_CI_AI, object_id, schema_id, ' + cast(database_id as nvarchar(10)) + N' from ' + quotename(name) + N'.sys.tables a join sys.databases b on database_id=' + cast(database_id as nvarchar(10)) + 'where a.name like ''account%'''
from sys.databases where database_id > 1 

and state = 0
and user_access = 0;

exec sp_executesql @sql;

---

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):

SET NOCOUNT ON
DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname)
DECLARE
     @SearchDb nvarchar(200)
    ,@SearchSchema nvarchar(200)
    ,@SearchTable nvarchar(200)
    ,@SQL nvarchar(4000)
SET @SearchDb='%'
SET @SearchSchema='%'
SET @SearchTable='%Account%'
SET @SQL='select ''?'' as DbName, s.name as SchemaName, t.name as TableName from [?].sys.tables t inner join [?].sys.schemas s on t.schema_id=s.schema_id WHERE ''?'' LIKE '''+@SearchDb+''' AND s.name LIKE '''+@SearchSchema+''' AND t.name LIKE '''+@SearchTable+''''

INSERT INTO @AllTables (DbName, SchemaName, TableName)
    EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY DbName, SchemaName, TableName

Answer 1

for a simple way to get all tables on the server, try this:

SET NOCOUNT ON
DECLARE @AllTables table (CompleteTableName nvarchar(4000))
INSERT INTO @AllTables (CompleteTableName)
    EXEC sp_msforeachdb 'select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id'
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY 1

it will return a single column that contains the server+database+schema+table name: sample output:

CompleteTableName
--------------------------------------------
YourServer.YourDatabase1.YourSchema1.YourTable1
YourServer.YourDatabase1.YourSchema1.YourTable2
YourServer.YourDatabase1.YourSchema2.YourTable1
YourServer.YourDatabase1.YourSchema2.YourTable2
YourServer.YourDatabase2.YourSchema1.YourTable1

if you are not on SQL Server 2005 or up, replace the DECLARE @AllTables table with CREATE TABLE #AllTables and then every @AllTables with #AllTables and it will work.

EDIT
here is a version that will allow a search parameter to be used on any part or parts of the server+database+schema+table names:

SET NOCOUNT ON
DECLARE @AllTables table (CompleteTableName nvarchar(4000))
DECLARE @Search nvarchar(4000)
       ,@SQL   nvarchar(4000)
SET @Search=null --all rows
SET @SQL='select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id WHERE @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name LIKE ''%'+ISNULL(@SEARCH,'')+'%'''

INSERT INTO @AllTables (CompleteTableName)
    EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY 1

set @Search to NULL for all tables, set it to things like 'dbo.users' or 'users' or '.master.dbo' or even include wildcards like '.master.%.u', etc.