Tag Archives: SQL Server

Find the database when table name is known

Requirement:

We have a table name or at least a part of it with us.  We want to know the database to which it belongs. Manually searching is not possible/ difficult as there are many databases having many more tables.

Solution:

We will create a stored procedure which accepts table name as a parameter. The procedure searches for the table name parameter across all user-defined databases in the server. If there is/are any match/matches the program will print out the server name. If there is no match, program prints the same.

Source code:

SET ANSI_NULLS ON
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Satyanarayana Kondapalli
-- Description: find the database in which the required table is present
-- =============================================
CREATE PROCEDURE getDBNameByTable @tblname VARCHAR(80)=NULL
AS
BEGIN
SET NOCOUNT ON;
--check if a parameter is passed as table name
IF (@tblname IS NULL)
BEGIN
PRINT 'Please pass a table name as parameter'
GOTO Error
END
IF OBJECT_ID('tempdb.dbo.#dbnames', 'U') IS NOT NULL
DROP TABLE #dbnames
IF OBJECT_ID('tempdb.dbo.#searchtable', 'U') IS NOT NULL
DROP TABLE #searchtable
CREATE TABLE #searchtable (table_name VARCHAR(80))
DECLARE @name VARCHAR(80)
DECLARE @searchquery VARCHAR(max)
DECLARE @count int
-- create a temp table with all database list
SELECT name INTO #dbnames from sys.databases where name not in ('model','msdb','master','tempdb')
DECLARE findmydb CURSOR FOR
SELECT name from #dbnames
OPEN findmydb
FETCH NEXT FROM findmydb INTO @name
SET @count=0
WHILE @@FETCH_STATUS = 0
BEGIN
--iterate every db to get tables list from each db
SELECT @searchquery='SELECT TABLE_NAME FROM '+@name+'.'+'information_schema.TABLES'
INSERT INTO #searchtable exec (@searchquery)
--if the table is found, print the db name
IF exists(SELECT table_name from #searchtable where table_name like '%'+@tblname+ '%')
BEGIN
SET @count=@count+1
PRINT @name
END
--truncate the table after each iteration
TRUNCATE TABLE #searchtable
FETCH NEXT FROM findmydb INTO @name
END
IF(@count=0)
--if table is not found at all
PRINT 'table does not exist in the server!'
CLOSE findmydb
DEALLOCATE findmydb
Error:return -1
END
GO

Sample:
EXEC getDBNameByTable 'TABLE_NAME'