Query to find the object across databases

When a new member joins our team first thing is expected out of him or her is to understand the database structure in order to deal with the issues clearly. It takes time to understand the database structure completely until then they rely on us to answer their quest to locate the object. And most of the times they tend to ask you about the location of the object when you yourself are wandering in the complex code that someone else has written.

This is where a small script to list the objects from the database becomes handy.

Following script has helped me on numerous occasions to locate the objects see if it helps you as well...

DECLARE @TAB TABLE(
   DbName VARCHAR(100),
   ObjectName VARCHAR(100),
   ObjectType VARCHAR(100)
)
INSERT INTO @TAB
EXEC sp_MSforeachdb '

SELECT ''?'',NAME,TYPE_DESC FROM [?].SYS.objects
WHERE LTRIM(RTRIM(NAME)) = ''SpecifyObjectNameHere'''

SELECT * FROM @TAB