Search Table Name of all database [ ค้นหาชือ Table ใน Database server ทั้งหมด ]

 --------------

-- Search Table Name inside database server
-- 1.
--------------
DECLARE @SearchTerm NVARCHAR(100) = '%TableName%';

EXEC sp_MSforeachdb '
USE [?];
SELECT 
    DB_NAME() AS DatabaseName,
    s.name AS SchemaName,
    t.name AS TableName,
    t.create_date AS CreateDate
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name LIKE @SearchTerm';
--------------
-- Search Table Name inside database server
--2.
--------------
DECLARE @TableName NVARCHAR(256) = 'TableName'; -- Table to find
DECLARE @Query NVARCHAR(MAX) = '';

SELECT @Query = @Query + 'SELECT ''' + name + ''' AS DatabaseName, s.name COLLATE Latin1_General_CI_AS, t.name COLLATE Latin1_General_CI_AS 
FROM [' + name + '].sys.tables t 
JOIN [' + name + '].sys.schemas s ON t.schema_id = s.schema_id 
WHERE t.name = ''' + @TableName + ''' UNION ALL '
FROM sys.databases 
WHERE state = 0; -- Only online databases

-- Remove the last 'UNION ALL'
IF LEN(@Query) > 0
BEGIN
    SET @Query = LEFT(@Query, LEN(@Query) - 10);
    EXEC sp_executesql @Query;
END
--------------