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

I needed something that I could use to search all my servers using CMS and search by server, DB, schema or table. This is what I found (originally posted by Michael Sorens here: How do I list all tables in all databases in SQL Server in a single result set? ).

SET NOCOUNT ON
DECLARE @AllTables TABLE
        (
         ServerName NVARCHAR(200)
        ,DBName NVARCHAR(200)
        ,SchemaName NVARCHAR(200)
        ,TableName NVARCHAR(200)
        )
DECLARE @SearchSvr NVARCHAR(200)
       ,@SearchDB NVARCHAR(200)
       ,@SearchS NVARCHAR(200)
       ,@SearchTbl NVARCHAR(200)
       ,@SQL NVARCHAR(4000)

SET @SearchSvr = NULL  --Search for Servers, NULL for all Servers
SET @SearchDB = NULL  --Search for DB, NULL for all Databases
SET @SearchS = NULL  --Search for Schemas, NULL for all Schemas
SET @SearchTbl = NULL  --Search for Tables, NULL for all Tables

SET @SQL = 'SELECT @@SERVERNAME
        ,''?''
        ,s.name
        ,t.name
         FROM [?].sys.tables t 
         JOIN sys.schemas s on t.schema_id=s.schema_id 
         WHERE @@SERVERNAME LIKE ''%' + ISNULL(@SearchSvr, '') + '%''
         AND ''?'' LIKE ''%' + ISNULL(@SearchDB, '') + '%''
         AND s.name LIKE ''%' + ISNULL(@SearchS, '') + '%''
         AND t.name LIKE ''%' + ISNULL(@SearchTbl, '') + '%''
      -- AND ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'',''SSISDB'')
           '
-- Remove the '--' from the last statement in the WHERE clause to exclude system tables

INSERT  INTO @AllTables
        (
         ServerName
        ,DBName
        ,SchemaName
        ,TableName
        )
        EXEC sp_MSforeachdb @SQL
SET NOCOUNT OFF
SELECT  *
FROM    @AllTables
ORDER BY 1,2,3,4

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.

declare @sql nvarchar(max);
set @sql = N'select cast(''master'' as sysname) as db_name, name collate Latin1_General_CI_AI, object_id, schema_id, cast(1 as int) as database_id  from master.sys.tables ';

select @sql = @sql + N' union all select ' + quotename(name,'''')+ ', name collate Latin1_General_CI_AI, object_id, schema_id, ' + cast(database_id as nvarchar(10)) + N' from ' + quotename(name) + N'.sys.tables'
from sys.databases where database_id > 1
and state = 0
and user_access = 0;

exec sp_executesql @sql;