SQL SERVER – Find Table in Every Database of SQL Server

Just a day ago, one of the Jr. Developer requested that if I can help her with finding one particular table in every database on SQL Server. We have many Database Server and on some of the Database Server we have nearly 200 databases on it. The requirement was to find out one particular table from all the database. This was not possible by visual inspection as it might take lots of time and human error was possible. She was aware of the system view sys.tables.

SELECT *
FROM sys.Tables
WHERE name LIKE '%Address%'

The limitation of query mentioned above is that it only searches in one database and user has to keep on changing database manually and run the query again. I wrote down following quick script which looks into all the database on the server and provides the database name, schema name and table containing searched word in its name.

Solarwinds
CREATE PROCEDURE usp_FindTableNameInAllDatabase
@TableName VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
SELECT name
FROM sys.databases
CREATE TABLE #TmpTable (DBName VARCHAR(256),
SchemaName VARCHAR(256),
TableName VARCHAR(256))
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = 'USE ' + @DBName + ';
INSERT INTO #TmpTable
SELECT '''+ @DBName + ''' AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName
FROM sys.tables
WHERE name LIKE ''%' + @TableName + '%'''
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable
DROP TABLE #TmpTable
GO
EXEC usp_FindTableNameInAllDatabase 'Address'
GO 


SQL SERVER - Find Table in Every Database of SQL Server findtable

If you liked this script and have similar useful script let me know and I will post it here.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Download FAQ Sheet – SQL Server in One Page
Next Post
SQL SERVER – Find Table in Every Database of SQL Server – Part 2

Related Posts

41 Comments. Leave new

  • Naomi Nosonovsky
    April 26, 2013 5:28 pm

    See also this tip that avoids the loop

    Reply
  • One minor addition I would suggest is that you should only look at tables where state_desc = ‘ONLINE’.

    Reply
  • i have a problem can you please help;
    it is on sql server 2008. in a single database i have 10 tables, in each table there is column “asset_id”, now, let us say there are 4 tables where asset_it=’ddn224′, how do i generate a list of table where asset_id =’ddn224′.

    Reply
  • nice

    Reply
  • search table in one db sp_Tables ‘%Address%’

    Reply
  • Thanks for the post, We can use sp_msforeachdb to get the same result in single line of code.

    Reply
  • Hi,
    We are using SQL Server 2012. In a database there are so many tables are existing, but some tables only using in our organisation Remaining are not using .So how to find only some tables which are using in one database?

    Reply
  • Hi,

    I am new to my organisation, They assigned me a work that to prepare a document of our database backup policy and database designing. So how do i start that assignment?
    Please help Me?

    Reply
  • From the above query if we need to get the data for particular user i.e; selecting the user from the front end application then how can we write the query

    Reply
  • Thanks…this helps!

    Reply
  • Bhavin Porecha
    May 30, 2017 4:18 pm

    declare @SQL nvarchar(max) = ”
    select @SQL = @SQL + ‘SELECT ”’ + d.name + ”’ as DatabaseName, t.name as TableName, c.name as ColumnName FROM ‘ + d.name + ‘.sys.tables t inner join ‘ + d.name + ‘.sys.columns c ON c.object_id = t.object_id WHERE c.name LIKE ”CIT%” AND t.Name LIKE ”tbP%” union all ‘
    from sys.databases d
    where d.name not in(‘master’, ‘tempdb’, ‘msdb’, ‘model’, ‘ReportServer’, ‘ReportServerTempDB’)

    set @SQL = left(@SQL, len(@SQL) – 10)

    exec sp_executesql @SQL

    Reply
  • In our environment we have more than 50 databases, but this script fetched details of only 12 databases. So, Pinal could you help here please.

    Reply
  • Hi,

    Please how do I make it possible to search database name too, Like making @DBname searchable as @TableName.

    Thanks

    Reply
  • CREATE PROCEDURE usp_FindTableNameInAllDatabase
    @TableName VARCHAR(256),
    @DatabaseName Varchar(max)
    AS
    DECLARE @DBName VARCHAR(256)
    DECLARE @varSQL VARCHAR(512)
    DECLARE @getDBName CURSOR
    SET @getDBName = CURSOR FOR

    SELECT name
    FROM sys.databases
    CREATE TABLE #TmpTable (DBName VARCHAR(256),
    SchemaName VARCHAR(256),
    TableName VARCHAR(256))
    OPEN @getDBName
    FETCH NEXT
    FROM @getDBName INTO @DBName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @varSQL = ‘USE [‘ + @DBName + ‘];
    INSERT INTO #TmpTable
    SELECT ”’+ @DBName + ”’ AS DBName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS TableName
    FROM sys.tables
    WHERE ([‘+ @DBName + ‘] LIKE ”[‘+ @DatabaseName +’]” AND name LIKE ”’+ @TableName+”’)’;

    EXEC (@varSQL)
    FETCH NEXT
    FROM @getDBName INTO @DBName
    END
    CLOSE @getDBName
    DEALLOCATE @getDBName
    SELECT *
    FROM #TmpTable
    DROP TABLE #TmpTable
    GO

    But when I execute the proc

    EXEC usp_FindTableNameInAllDatabase ‘Logins’,’EXTRA_122-126ChanceryLane’;

    I get this Error Message

    Msg 207, Level 16, State 1, Line 50
    Invalid column name ‘master’.

    Please me look into in this, Have tried different ways

    Reply
  • does it work for db name having space e.g. db name : “Demo 123”; i am getting error

    Reply
  • did you figure this out ?

    Reply

Leave a Reply

Menu