As a SQL Server developer, you may come across situations where you need to find a specific table in multiple databases. Manually inspecting each database can be time-consuming and prone to human error. In this article, we will discuss a script that can help you search for a table in all databases on a SQL Server.

Let’s say you have a SQL Server with multiple databases, and you want to find a table named “Address” in all of them. The traditional approach would be to switch to each database and run a query using the sys.tables system view. However, this can be tedious and inefficient.

To simplify this process, we can create a stored procedure that iterates through all the databases on the server and searches for the desired table. Here’s an example script:

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

This script creates a stored procedure called usp_FindTableNameInAllDatabase that takes a table name as a parameter. It uses a cursor to iterate through all the databases on the server and dynamically generates SQL statements to search for the table in each database. The results are stored in a temporary table called #TmpTable, which is then queried to retrieve the database name, schema name, and table name.

To use this script, simply execute the stored procedure with the desired table name as the parameter. In our example, we searched for the table named “Address”. The script will return the database name, schema name, and table name for all occurrences of the searched table.

If you find this script useful, feel free to use it in your projects. You can also modify it to suit your specific requirements. If you have any similar useful scripts, let us know, and we’ll be happy to share them here.

Happy coding!