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://www.SQLAuthority.com)
pinaldave
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
[...] 5, 2008 by pinaldave Long time blog reader and SQL Server Expert Simon Worth has suggested two additional method to achieve same results as described in article SQL SERVER - [...]
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)
[...] May 16, 2008 by pinaldave Previously I wrote two articles about SQL SERVER - Find Table in Every Database of SQL Server [...]
Your articles are really very conducive to solve my problems.