Snippet Name: Database_Object_Search_Script

Tags:

Created Date: 2025-06-16 13:12:48
Last Modified Date: 2025-05-31 10:31:48

Dependencies:

DBA Notes:

Sample Output:

Title: Database Object Search Script
Author: user
--desc: searches objects and columns that contain a string, but in all databases.
DECLARE @findcolumn VARCHAR(128) = 'sp_RemoveViewsWithMaskingDependancies'
 
IF OBJECT_ID('tempdb.[dbo].[#tmp]') IS NOT NULL 
DROP TABLE [dbo].[#tmp]; 
CREATE TABLE [dbo].[#tmp] ( 
[DatabaseName] sysname                          NOT NULL,
[TableFound]   sysname                          NOT NULL,
[ColumnFound]  NVARCHAR(128)                        NULL,
[ObjectType]   NVARCHAR(60)                         NULL);
--declare @findcolumn varchar(128)= 'MthHour'
 --print object_name(@@PROCID) 
DECLARE @cmd VARCHAR(MAX) = '
INSERT INTO [#tmp]([DatabaseName],[TableFound],[ColumnFound],[ObjectType])
 SELECT 
   ''[?]'' AS DatabaseName,
   TableFound, 
   ColumnFound,
   ObjectType 
 FROM   
   (       
    SELECT 
      1 AS SortOrder,
      objz.name AS TableFound,
      '''' AS ColumnFound,
      objz.type_desc AS ObjectType              
    FROM [?].sys.objects objz 
      WHERE objz.name LIKE ''%' + @findcolumn + '%''  
      AND objz.type_desc IN(''SYSTEM_TABLE'',
       ''VIEW'',
       ''SQL_TABLE_VALUED_FUNCTION'',
       ''SQL_STORED_PROCEDURE'',
       ''SQL_INLINE_TABLE_VALUED_FUNCTION'',
       ''USER_TABLE'',
       ''SQL_SCALAR_FUNCTION'')
    UNION ALL   
    SELECT 
      2 AS SortOrder,
      objz.name AS TableFound,
      colz.name AS ColumnFound,
      objz.type_desc AS ObjectType                
    FROM [?].sys.objects objz              
      INNER JOIN [?].sys.columns colz 
        ON objz.object_id=colz.object_id              
    WHERE colz.name LIKE ''%' + @findcolumn + '%'' 
          AND objz.type_desc IN(''SYSTEM_TABLE'',
       ''VIEW'',
          ''USER_TABLE'')    
   )  X       
   ORDER BY   
     SortOrder,
     TableFound,
     ColumnFound ';

EXEC [sys].[sp_MSforeachdb] @cmd;
SELECT * FROM [#tmp];

Author: ChatGPT
Your script searches for database objects and columns that match a specific string in all databases. It leverages SQL Server's `sp_MSforeachdb` to loop through each database and performs two searches:

1. Object names (tables, views, functions, and stored procedures) that contain the specified string.
2. Column names in tables and views that contain the specified string.

The results are inserted into a temporary table, `#tmp`, and finally, the script outputs the results.

Here???s a breakdown of the query:

1. **Temporary Table Creation (`#tmp`)**: This table stores the results of the search. Each row will contain the database name, object name (table/view/etc.), column name (if applicable), and the type of object.

2. **Search Logic**:
   - First, the script searches for objects whose name matches the string in the `@findcolumn` variable across a range of object types: `SYSTEM_TABLE`, `VIEW`, `SQL_TABLE_VALUED_FUNCTION`, etc.
   - Then, it searches for columns whose names match the string in `@findcolumn`, but only for `SYSTEM_TABLE`, `VIEW`, and `USER_TABLE`.

3. **Execution Using `sp_MSforeachdb`**: The `@cmd` variable holds the dynamic SQL that runs across all databases using `sp_MSforeachdb`. This system stored procedure executes a command for each database in the instance.

4. **Final Output**: After all databases are searched, the script returns the contents of the `#tmp` table.

### Optimizations and Considerations:
- **Performance**: This script could take a while to run on large systems with many databases or large schemas. Consider filtering databases (e.g., excluding system databases) if you're only interested in user databases.
  
- **`sp_MSforeachdb`**: This stored procedure is undocumented and can sometimes miss databases. For a more reliable approach, consider using a cursor to iterate through the databases.

Would you like an example with a cursor-based approach?