title: How do I list all tables in all databases in SQL Server in a single result set?
body: <p>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 <em><strong>single result set</strong></em>. This precludes the otherwise excellent answer from <a href="http://blog.sqlauthority.com/2009/04/26/sql-server-list-all-the-tables-for-all-databases-using-system-tables/" rel="noreferrer">Pinal Dave</a>:</p>
  <pre><code>sp_msforeachdb 'select &quot;?&quot; AS db, * from [?].sys.tables'
  </code></pre>
  <p>The above stored proc generates one result set <em><strong>per database</strong></em>, 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 &quot;find&quot; tool: if I add a clause like <code>WHERE tablename like '%accounts'</code> then it would tell me where to find my BillAccounts, ClientAccounts, and VendorAccounts tables regardless of which database they reside in.</p>
  <hr />
  <h2>2010.05.20 Update, about 20 minutes later...</h2>
  <p>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!</p>
  <pre><code>declare @sql nvarchar(max);
  set @sql = N'select b.name as &quot;DB&quot;, a.name collate Latin1_General_CI_AI as &quot;Table&quot;, 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 &quot;DB&quot;, 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 &gt; 1 
  
  and state = 0
  and user_access = 0;
  
  exec sp_executesql @sql;
  </code></pre>
  <hr />
  <h2>2010.05.24 Update -- New Front runner!</h2>
  <p>The feedback and answers have been great. Continued collaborative participation has led to a <strong>new frontrunner</strong>: KM's answer from May 21!</p>
  <p>Here are the issues I uncovered with Remus' solution:</p>
  <p><strong>Major issue:</strong> 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 <em><strong>no</strong></em> filtering (i.e. omitting the <code>WHERE</code> clause) I received this error on several DBs that I do not have permission to access:</p>
  <blockquote>
  <p>The server principal &quot;msorens&quot; is not able to access the database
  &quot;ETLprocDB&quot; under the current security context.</p>
  </blockquote>
  <p>The query <em><strong>will</strong></em> succeed with some filtering clauses--those that do not touch the DBs outside my access level.</p>
  <p><strong>Minor issue:</strong> 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.</p>
  <p><strong>Minor issue:</strong> Duplicate code--the loop setup essentially duplicates the loop body. I understand the rationale but it is just a pet peeve of mine...</p>
  <p>KM's answer is not afflicted by these issues. The stored proc <code>sp_msforeachdb</code> 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.</p>
  <p>I am posting next my modifications to KM's answer based on my personal preferences. Specifically:</p>
  <ul>
  <li>I have removed the server name as it does not really add anything in the result set.</li>
  <li>I have split the name components into their own fields in the result set (db name, schema name, and table name).</li>
  <li>I have introduced separate filters for each of the three fields.</li>
  <li>I have added sorting by the three fields (which can be modified to your preferences).</li>
  </ul>
  <p>Here is my modification to KM's code (with a sample filter applied just to the table name):</p>
  <pre><code>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
  </code></pre>