CODEWITHSUNDEEP
sqlsql-serverssms
baineschile
baineschile

Reputation: 149

SQL - Search for table name across all databases on server

I thought this would be pretty straightforward, but I have about 80 databases in the server I am looking at, each database has 5-500 tables.

I am wondering how i can search for a TABLE NAME across everything. I tried a basic

SELECT 
*

FROM sys.tables

but I only get 6 results.

Upvotes: 5

Views: 7492

Answers (7)

Bartosz X
Bartosz X

Reputation: 2798

Object finder (any object from sys.objects, but can be restricted to specific search):

DECLARE @object_name nvarchar(128) = N'temp'

DECLARE @sql nvarchar(2000);
IF OBJECT_ID('tempdb..#finder') IS NOT NULL DROP TABLE #finder;
CREATE TABLE #finder ([db] sysname, [schema] sysname, [object_name] sysname, [object_type] nvarchar(60));
SET @sql =  N'USE [?]; 
                    INSERT INTO #finder([db], [schema], [object_name], [object_type])
                    SELECT 
                    [db] = DB_NAME(),
                    [schema] = OBJECT_SCHEMA_NAME([object_id]),
                    [object_name] = [name],
                    [object_type] = [type_desc]
                    FROM sys.objects
                    WHERE [name] LIKE N''%' + @object_name + N'%'''
EXEC sp_MSforeachdb @sql
SELECT * FROM #finder

enter image description here

Upvotes: 0

Osman Işık
Osman Işık

Reputation: 1

DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @sql = N'', @src = N' UNION ALL 
SELECT ''$d'', 
s.name COLLATE SQL_Latin1_General_CP1_CI_AI,
t.name COLLATE SQL_Latin1_General_CP1_CI_AI
FROM [$d].sys.schemas AS s
INNER JOIN [$d].sys.tables AS t
ON s.[schema_id] = t.[schema_id] WHERE t.name like 
''%YOUR_TABLE_NAME%''';

SELECT @sql = @sql + REPLACE(@src, '$d', name)
FROM sys.databases
WHERE database_id > 4
AND [state] = 0
AND HAS_DBACCESS(name) = 1;

SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));

--PRINT @sql;
EXEC sys.sp_executesql @sql;

Upvotes: 0

user14457116
user14457116

Reputation: 1

Dim sql As String = ("Select * from " & ComboboxDatabaseName.Text & ".sys.tables")

use this key

Upvotes: -2

user1443098
user1443098

Reputation: 7625

This version uses FOR XML PATH('') instead of string concatenation, eliminates the default system databases, handles databases with non-standard names and supports a search pattern. 

DECLARE @pattern NVARCHAR(128) = '%yourpattern%';
DECLARE @sql NVARCHAR(max) = STUFF((
    SELECT 'union all select DatabaseName = name from ' + QUOTENAME(d.name) + '.sys.tables where name like ''' + @pattern + ''' '
    FROM sys.databases d
    WHERE d.database_id > 4
    FOR XML path('')
    ), 1, 10, '');

EXEC sp_executesql @sql;

You might need to write:

select DatabaseName = name collate Latin1_General_CI_AS

I know I did.

Upvotes: 4

Daniel Marcus
Daniel Marcus

Reputation: 2686

Here's a bit of a simpler option using dynamic sql. This will get you the name of all tables in every database in your environment:

declare @table table (idx int identity, name varchar(max))
insert @table
select name from master.sys.databases

declare @dbname varchar(max)
declare @iterator int=1
while @iterator<=(select max(idx) from @table) begin

select @dbname=name from @table where idx=@iterator

exec('use ['+@dbname+'] select name from sys.tables')

set @iterator=@iterator+1
end

select * from @table

Upvotes: 0

Cristian Lupascu
Cristian Lupascu

Reputation: 40506

This is a bit of a hack, but I think it should work:

sp_msforeachdb 'select ''?'' from ?.information_schema.tables where table_name=''YourTableName''';

It will output the names of the DBs that contain a table with the given name.

Here's a version using print that is a little better IMHO:

sp_msforeachdb '
    if exists(select * from ?.information_schema.tables where table_name=''YourTableName'')
        print ''?'' ';

The above queries are using ms_foreachdb, a stored procedure that runs a given query on all databases present on the current server.

Upvotes: 6

Sean Lange
Sean Lange

Reputation: 33571

Just because I really dislike loops I wanted to post an alternative to answers already posted that are using cursors.

This leverages dynamic sql and the sys.databases table.

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'select DatabaseName = name from [' + name + '].sys.tables where name = ''YourTableName'' union all '
from sys.databases

set @SQL = stuff(@SQL, len(@SQL) - 9, 11, '') --removes the last UNION ALL

exec sp_executesql @SQL

Upvotes: 2

Related Questions