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.

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

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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

30 thoughts on “SQL SERVER – Find Table in Every Database of SQL Server

  1. 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

  2. Pingback: SQL SERVER - Find Table in Every Database of SQL Server - Part 2 Journey to SQL Authority with Pinal Dave

  3. 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)

  4. Pingback: SQL SERVER - Find Table in Every Database of SQL Server - Part 3 Journey to SQL Authority with Pinal Dave

  5. 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..

  6. @Praveen

    — 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]
    ,CHARACTER_MAXIMUM_LENGTH [Lenth]
    ,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.

  7. 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.

  8. @Sandya

    Exec sp_depends ‘table_name’

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

    ~IM

  9. 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

    Regards
    Arun PK

  10. 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?

    Thanks,

  11. 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.

  12. 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

  13. 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

  14. 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’

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

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

  16. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

  17. 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′.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s