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.

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.

CREATE PROCEDURE usp_FindTableNameInAllDatabase
@TableName VARCHAR(256)
FROM sys.databases
SchemaName VARCHAR(256),
TableName VARCHAR(256))
OPEN @getDBName
FROM @getDBName INTO @DBName
SET @varSQL = 'USE ' + @DBName + ';
SELECT '''+ @DBName + ''' AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName
FROM sys.tables
WHERE name LIKE ''%' + @TableName + '%'''
EXEC (@varSQL)
FROM @getDBName INTO @DBName
CLOSE @getDBName
FROM #TmpTable
EXEC usp_FindTableNameInAllDatabase 'Address'

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)

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

42 Comments. Leave new

  • Simon Worth
    May 9, 2008 2:36 am

    Or you could built in functionality that SQL Server provides and use 1 line of code

    sp_msforeachdb “SELECT ‘?’ DatabaseName, Name FROM ?.sys.Tables WHERE Name LIKE ‘%address%'”

    If you did indeed want to keep the results in a table you could do that too

    CREATE TABLE #TableNameResults (DatabaseName VARCHAR(100) NOT NULL, TableName VARCHAR(100) NOT NULL)
    INSERT INTO #TableNameResults EXEC sp_msforeachdb “SELECT ‘?’ DatabaseName, Name FROM ?.sys.Tables WHERE Name LIKE ‘%address%'”

    SELECT * FROM #TableNameResults

    DROP TABLE #TableNameResults

    • i think the above query could be enhanced by using contains or freetext

      because its returning every thing related to the search word. but if we want to return only the word that we have specified.. for example

      instead of returning every thing related to address it should return only adress

  • How about a query that writes queries? Using sysdatabases table in the MASTER database you can write a query that looks for a table name in the sysobjects table in each database. In my case I’m looking for a ‘Folders’ table.

    Running this query…

    USE MASTER; SELECT ‘USE ‘ + name + ‘; SELECT ”’ + name + ”’ as DBName, name as TableName FROM sysobjects where name like ”Folders”’ FROM sysdatabases WHERE dbid > 5 ORDER BY dbid

    You get these queries…

    USE Northwind; SELECT ‘Northwind’ as DBName, name as TableName FROM sysobjects where name like ‘Folders’
    USE Q_SanJoaquin; SELECT ‘Q_SanJoaquin’ as DBName, name as TableName FROM sysobjects where name like ‘Folders’
    USE ClerkConv; SELECT ‘ClerkConv’ as DBName, name as TableName FROM sysobjects where name like ‘Folders’
    USE QuestysEnt; SELECT ‘QuestysEnt’ as DBName, name as TableName FROM sysobjects where name like ‘Folders’
    USE Q_Police; SELECT ‘Q_Police’ as DBName, name as TableName FROM sysobjects where name like ‘Folders’

    And running those queries, you get this….

    DBName TableName
    ——— ——————————————————————————————————————————–

    (0 row(s) affected)

    DBName TableName
    ———— ——————————————————————————————————————————–
    Q_SanJoaquin Folders

    (1 row(s) affected)

    DBName TableName
    ——— ——————————————————————————————————————————–

    (0 row(s) affected)

    DBName TableName
    ———- ——————————————————————————————————————————–
    QuestysEnt Folders

    (1 row(s) affected)

    DBName TableName
    ——– ——————————————————————————————————————————–
    Q_Police Folders

    (1 row(s) affected)

  • Your articles are really very conducive to solve my problems.

  • Is there a way to query an entire sql server looking for a certain column? am trying to create an application where the users can search our entire sql server by a column name.I want the query to list the table name,database name,column name,column data type.
    Any ideas or sample code will be greatly appreciated.

    Thanks much..

  • Imran Mohammed
    July 24, 2009 8:49 am


    — Either make this a stored procedure or change value of @Column_Name,

    Code Starts here,

    Declare @Column_Name sysname
    Set @Column_Name = ‘eid’

    Declare @temp_variable table ([Database Name] sysname, [Schema Name] sysname, [Table Name] sysname, [Column Name] sysname , [Ordinal Position]Int, [Is Null] varchar(3), [Data type] varchar(50), [Length] int , [Precision] Int, [Scale] Int)

    Declare @Sqlcmd1 nvarchar(4000)

    Set @Sqlcmd1 =
    ‘EXEC sp_msforeachdb

    USE ?
    Select TABLE_CATALOG [Database Name]
    ,TABLE_SCHEMA [Schema Name]
    ,TABLE_NAME [Table Name]
    ,COLUMN_NAME [Column Name]
    ,ORDINAL_POSITION [Ordinal Position]
    ,IS_NULLABLE [Is Null]
    ,DATA_TYPE [Data Type]
    ,NUMERIC_PRECISION [Precision]
    ,NUMERIC_SCALE [Scale]
    From Information_Schema.columns
    Where COLUMN_NAME = ””’+@Column_Name+””””

    Insert into @temp_variable Exec Sp_ExecuteSQL @Sqlcmd1

    Select * from @temp_variable

    ~ IM.

  • Can we find in all the stored procedures where the table is used?
    If we alter the table and add a column, and want to see where all the table is used in the stored procedures and functions.
    How do we achieve that.

  • Imran Mohammed
    November 4, 2009 8:32 am


    Exec sp_depends ‘table_name’

    Result set returned, will contain all dependencies, like which other tables, views, stored procedure depend on this table


  • I want to know how to see all tables of particular database.
    What is the SQL query for that.

  • Pinal Dave,

    Can please tell me how to get the order of table created based on the relationship created.

    for Example : 1. Master_table
    2. Transaction_Table

    Arun PK

  • Hi, I am using this sproc and it works fine. I want to extend it so that it will also drop those dbs.
    I am having a problem with that.
    Would you please give me a hint?


  • Little correction in my above post.
    I want to generate separate script that reads the dbname and table name from the table and delete it.
    So, I would not drop temp table in 1st sproc.

  • how to find total no of table in a database

  • I am trying to create a sql script to query mutliple databases that contain the same exact tables. I want to create a table to have a column for the database name and a table for a specific column of a table. I am getting this error Msg

    208, Level 16, State 1, Line 34
    Invalid object name ”+ @name +’.dbo.tbldoc’.

    the @name is set as:

    set @name=(‘select name from sys.databases where [name] like ”Z%”’)

    declare IMAGEC cursor for
    select count(pgcount) as ImageCount from [‘+ @name +’].dbo.tbldoc

  • Hi All!

    how to find a particular Table view is used in more than one query?????

  • Hi Pinal,
    I need a store procedure…
    Say there are 26 databases in a server:

    o NAccountA

    o NAccountB

    o NAccountC

    o …

    o NAccountX

    o NAccountY

    o NAccountZ

    And every database has a table Employee with fields Name and Salary.
    Please write a Stored Procedure to get the total employee and salary of each databases (including total at the bottom of the result). The output should be:

    Database Employee Count Total Salary
    NAccountA 200 $25,180.00
    NAccountB 180 $18,860.00
    … … …
    NAccountZ 180 $18,860.00
    Total: 2300 $562,520.00

  • use this

    EXECUTE sp_msforeachdb ‘USE ? SELECT *
    FROM sys.Tables
    WHERE name LIKE ”%LuceneCollection%”’

  • How about finding table with ## in front of table name?
    for example ‘##tablename’

  • Already find!!!

    SELECT Table_Catalog, Table_Name FROM information_schema.tables
    WHERE table_name like ‘%TEMP%’

  • use master
    SELECT DISTINCT DB_NAME(database_id) FROM [sys].[dm_db_index_operational_stats](NULL,NULL,NULL,NULL)
    WHERE OBJECT_NAME(object_id,database_id) = ‘TableName’

  • select count(*) From information_schema.tables
    where table_type = ‘base table’

    Work perfect to get count of table in a database..


Leave a Reply Cancel reply

Exit mobile version