SELECT so.name, so.object_id AS ID, so.type_desc,SCHEMA_NAME ( so.schema_id)
FROM sys.all_objects so
WHERE so.name LIKE '%'+@search+'%'
I want to add the object database name to this query but I don't know where to find the key field to do the join.
The sys.objects view includes all objects created within the current database (including those shipped by Microsoft, which are marked in the is_ms_shipped column). These objects can be tables, views, stored procedures, constraints, user-defined functions, etc.
The sys.system_objects view includes all system objects. These objects are accessible from any database, but are not stored within the current database. Their definition is in the Resource Database (mssqlsystemresource.mdf), a hidden, read-only database, which cannot be acessed in the usual way.
The sys.all_objects view contains a UNION between sys.objects and sys.system_objects. If you want to search for objects that are created by the user, you should use the sys.objects view instead of the sys.all_objects view.
If you want to know the name of the current database, you can use the DB_NAME() function.
If you want to search for an object in all databases, you could use a cursor or (if it's a one-time thing) the undocumented stored procedure sp_MSforeachdb:
DECLARE @SQL NVARCHAR(MAX)
SET @SQL='USE ? SELECT name, object_id, type_desc, SCHEMA_NAME(schema_id), DB_NAME() FROM sys.objects WHERE name LIKE ''%'+REPLACE(@search,'''','''''''')+'%'''
EXEC sp_MSforeachdb @SQL
DECLARE @search sysname
SET @search='xyz'
SELECT DB_NAME()[DATABASE],
so.name,
so.object_id AS ID,
so.type_desc,
SCHEMA_NAME ( so.schema_id)
FROM sys.all_objects so
WHERE so.name LIKE '%'+@search+'%'
Although it's an old post, but still very useful & applicable, therefore here is my addition. Solution uses sp_MSForEachDb (as other answers) but renders output in much cleaner/readable and exportable format.
Essentially, as by default the output generated by the sp_MSForEachDb is split into separate panes which makes it very difficult to copy/export out all the result-sets in one go.
To overcome above issue and to get all the resultsets in an exportable output (as a single resultset), all we need to do is first store the resultsets in to a #temp table then retrieve output from there, following is the full working example that searches an object-name in each database (along with other essential information including the database-name as asked in the question):
DECLARE @obj_to_find VARCHAR(999) = 'your_object_name_here'
drop table if exists #searchResults
SELECT top 1 name, object_id, SCHEMA_NAME(schema_id) AS [schema_name], DB_NAME() [DB_Name], type, type_desc, create_date, modify_date
INTO #searchResults FROM sys.objects WHERE 0 = 1;
DECLARE @qry NVARCHAR(999);
SET @qry='USE [?]; INSERT INTO #searchResults SELECT name, object_id, SCHEMA_NAME(schema_id) AS [schema_name], DB_NAME() [DB_Name], type, type_desc, create_date, modify_date FROM sys.objects WHERE name LIKE ''%'+REPLACE(@obj_to_find,'''','''''''')+'%'''
exec sp_MSforeachdb @qry
select * from #searchResults
In addition to above, one can install free search tool from Red-Gate© called "SQL Search" which does excellent job (disclaimer: I'm not being sponsored for mentioning this tool).
