Find a Database with a Particular Table or Find a Table in Every Database of SQL Server

Display all the names of databases containing particular table

I got it done through following query:

SELECT name
FROM sys.databases
WHERE  CASE WHEN state_desc = 'ONLINE' 
         THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[heartbit]', 'U')
       END IS NOT NULL

Find a database with a particular table OR Find a table in every database of SQL Server

Okay, if you're just wanting to find each database that contains a particular table, and aren't going to be querying the table, then you can just do:

create table #t (
    DBName sysname not null
)
go
exec sp_MSforeachdb 'use [?]; if OBJECT_ID(''dbo.mytable'') is not null insert into #t (DBName) select ''?'''
go
select * from #t
go
drop table #t

(If you're not using multiple schemas in your databases, you won't need to specify dbo in the OBJECT_ID call, otherwise I use it to avoid finding tables in the wrong schema)

Find a string by searching all tables in SQL Server

If you are like me and have certain restrictions in a production environment, you may wish to use a table variable instead of temp table, and an ad-hoc query rather than a create procedure.

Of course depending on your sql server instance, it must support table variables.

I also added a USE statement to narrow the search scope

USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

Searching all tables in database containing specific column

Using INFORMATION_SCHEMA.COLUMNS:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'id'

Get all table names of a particular database by SQL query?

Probably due to the way different sql dbms deal with schemas.

Try the following

For SQL Server:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='dbName'

For MySQL:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbName'

For Oracle I think the equivalent would be to use DBA_TABLES.

Search all tables, all columns for a specific value SQL Server

I've just updated my blog post to correct the error in the script that you were having Jeff, you can see the updated script here: Search all fields in SQL Server Database

As requested, here's the script in case you want it but I'd recommend reviewing the blog post as I do update it from time to time

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'
 
 
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Updated and tested by Tim Gaunt
-- http://www.thesitedoctor.co.uk
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
-- Date modified: 03rd March 2011 19:00 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
SET NOCOUNT ON
 
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @TableName IS NOT NULL
 
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
 
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
         
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )
 
        IF @ColumnName IS NOT NULL
         
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END   
END
 
SELECT ColumnName, ColumnValue FROM #Results
 
DROP TABLE #Results

How to find, across multiple databases, a specific table (common to most/all) that is not empty

In both methods replace <tablename> with the table name

Using sp_foreachdb

You can use sp_foreachDb

CREATE TABLE ##TBLTEMP(dbname varchar(100), rowscount int)

DECLARE @command varchar(4000) 
SELECT @command = 
'if exists(select 1 from [?].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =''<TABLE NAME>'') insert into ##TBLTEMP(dbname,rowscount) select ''[?]'',count(*) from [?].dbo.<tablename>' 
EXEC sp_MSforeachdb @command

SELECT * FROM ##TBLTEMP WHERE rowscount > 0

DROP TABLE ##TBLTEMP

Using CURSOR

CREATE TABLE ##TBLTEMP(dbname  varchar(100), rowscount int)
DECLARE @dbname  Varchar(100), @strQuery varchar(4000)

DECLARE csr CURSOR FOR SELECT [name] FROM sys.databases
FETCH NEXT FROM csr INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @strQuery = 'if exists(select 1 from [' + @dbname +'].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =''<TABLE NAME>'') INSERT INTO ##TBLTEMP(dbname,rowscount) SELECT ''' + @dbname + '' ', COUNT(*) FROM [' + @dbname + '].[dbo].<table name>'

EXEC(@strQuery)

FETCH NEXT FROM csr INTO @dbname

END

CLOSE csr
DEALLOCATE csr

SELECT * FROM ##TBLTEMP where rowscount > 0

References

Sp MSforeachDB
Run same command on all SQL Server databases without cursors
DECLARE CURSOR (Transact-SQL)

Search a table name across multiple databases SQL SERVER 2019

With the following query you can retrieve the names of all databases.

SELECT name, database_id, create_date  
FROM sys.databases ;  
GO  

OR-----------------------------------

SELECT *
FROM sys.databases ;  
GO

If you want the names of tables in a database, you can use the following query

USE master 
GO
SELECT * 
FROM information_schema.tables
GO  

OR----------------------------

USE model 
GO
SELECT * 
FROM information_schema.tables
GO  

OR-----------------------

USE ... 
GO
SELECT * 
FROM information_schema.tables
GO

The following code retrieves all databases and returns all tables of each database in a while loop.

Declare @tempTable Table
(
  name varchar(100)
)
--Container to Insert records in the inner select for final output

Insert into @tempTable 
SELECT name
FROM sys.databases 

-- Keep track of @tempTable record processing
Declare @name varchar(100)
Declare @SQL VarChar(1000)

While((Select Count(*) From @tempTable)>0)
Begin
  Set @name=(Select Top 1 name From @tempTable)
  --get all db tables
  SELECT @SQL = 'USE ' + QUOTENAME(@name) + ' SELECT * FROM information_schema.tables' 
  Exec (@SQL)
  Delete @tempTable Where name=@name
End

If you want to use SQL LIKE Operator, follow the code below

SELECT @SQL = 'USE ' + QUOTENAME(@name) + ' SELECT * FROM information_schema.tables WHERE table_name LIKE ''%a%'''