Technical Article

Find database where table is located

,

declare @TableName varchar(500) = 'partofmytablename';

exec master.dbo.proc_FindTable @TableName;

go

USE [master]
GO
/****** 
Objective: Find a table within an instance of SQL Server (version 2012) using wildcards
Date: 14/04/2014
Author: Trevor Makoni
******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[proc_FindTable]
(
@TableName varchar(500)
)
AS
BEGIN;
SET @TableName = LTRIM(RTRIM(ISNULL(@TableName, '')));
IF @TableName = ''
RETURN;

DECLARE @DB TABLE(id int identity(1,1), dbName sysname);
DECLARE @RESULTS TABLE(
table_catalog varchar(500),
table_schema varchar(500),
table_name varchar(500));

INSERT INTO @DB
(dbName)
SELECT db.name 
FROM sys.databases db;

declare
@min int = 1,
@dbName sysname,
@sql varchar(max);

while @min <= (select MAX(id) from @DB)
begin;
set @dbName = (select dbName from @DB where id = @min);
set @sql = '
select table_catalog,table_schema,table_name
from ['+@dbName+'].information_schema.tables
where table_name like ''%'+@TableName+'%'';';
insert into @RESULTS
exec (@sql);
set @min += 1;
end;

select
table_catalog [Database],
table_schema [Schema],
table_name  [Table]
from @RESULTS;
END;

Rate

3 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (2)

You rated this post out of 5. Change rating