How to Find Object ( Table.View,Stored Procedure,Function etc.) in All the Database in SQL Server - TSQL Tutorial / SQL Server Tutorial

On daily basis we come across different questions and scenarios as Database developers and DBAs. Sometime users just stop by and they ask us questions and tell us that they have a table in one of the database and if we can take a look for them. When we try to find out the table, we don't see the table.The table might be in different Database and user is not providing correct information.

The below script can be used to find out any object on Entire SQL Server Instance. you can use this script

To find a Table in all the Databases on SQL Server Instance
To find a Stored Procedure in All the Databases on SQL Server Instance
To find a view on entire SQL Server Instance
To find a function in any database on SQL Server Instance.

In short any object can be found by using below script. You only have to provide the name of object and it will return you information with Database Name, Schema Name, Object Name, Type and Type Description. The below code is also one of the example where we need to change the database name in our query and we can use Dynamic SQL to perform that.

--Provide The Object Name such as TableName,Stored Procedure, View,
--Function etc that you find on Entire SQL SERVER INSTANCE
DECLARE @ObjectName VARCHAR(100)
SET @ObjectName='Customer' -- Change the Customer to your ObjectName

------------------------------------------------------------------------------------
    IF OBJECT_ID('tempdb..##Object') IS NOT NULL
        DROP TABLE ##Object

    CREATE TABLE ##Object (
        DatabaseName VARCHAR(500)
        ,ObjectName VARCHAR(500)
        ,SchemaName VARCHAR(100),
        ObjectType VARCHAR(10),
        ObjectDesc VARCHAR(100)
        )

--Use Cursor to Loop through Databases to Find Object in SQL Server Instance Databases
    DECLARE @DatabaseName AS VARCHAR(500)

    DECLARE DBCursor CURSOR
    FOR
    SELECT NAME
    FROM sys.databases
    
    OPEN DBCursor

    FETCH NEXT
    FROM DBCursor
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        
        EXEC (
                N'USE ' + @DBName + N'; EXEC(''
Insert into ##Object
Select DB_Name() AS DatabaseName,
name as ObjectName,
schema_name(schema_id) AS SchemaName,
Type,
type_desc From sys.objects
where is_ms_shipped=0
'');'
                );

        FETCH NEXT
        FROM DBCursor
        INTO @DatabaseName
    END

    CLOSE DBCursor

    DEALLOCATE DBCursor

    --Return the Object Name with DatabaseName,SchemaName,Type and Type Descripton. 
   
    SELECT *
    FROM ##Object
    where ObjectName=@ObjectName

Find object ( Table,Stored Procedure, Function,View etc. in SQL Server)

Video Tutorial : How to Search Object Name on SQL Server Instance