CODEWITHSUNDEEP
sqlt-sqlschema
Franco Talamo
Franco Talamo

Reputation: 21

How to get the database name from a sys.all_objects consult

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.

Upvotes: 1

Views: 10648

Answers (3)

Eddie Kumar
Eddie Kumar

Reputation: 1480

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

HTH.

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).

Upvotes: 0

Amit Patel
Amit Patel

Reputation: 1

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+'%'

Upvotes: 0

Razvan Socol
Razvan Socol

Reputation: 5684

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

Upvotes: 4

Related Questions