How to get a list of objects from all databases on a SQL Server host

SQL Server has a very complete metadata catalog, which comes in handy a lot more often than you might think. One limitation of it though, is that all the metadata is stored on a per-database basis, which makes it difficult to correlate information between databases. (The metadata is stored in system tables in each individual database.)

Last week, I needed to gather a list of stored procedures from multiple databases. I found some solutions online, but most of them involved spitting out multiple results sets, which wasn't particularly useful for me.  After poking around for a little while, I came up with the following solution:

USE [master]
GO

DECLARE @SchemaName VARCHAR(50) = 'dbo';
DECLARE @sql AS VARCHAR(MAX) = '';

SELECT 
 @sql = @sql +
 'SELECT ''['+name+'].['+@SchemaName+'].'' + name AS procedure_name ' +
 'FROM ['+name+'].sys.procedures ' + 
 'WHERE schema_id = '+ 
  '(SELECT schema_id ' +
  ' FROM ['+name+'].sys.schemas ' + 
  ' WHERE name = '''+@SchemaName+''') ' +
 'UNION ' + CHAR(10)
FROM sys.databases
WHERE [state] = 0

SET @sql = LEFT(@sql, LEN(@sql)-8)

EXEC(@sql)
GO

(The sys.databases table is available in every database, not just master. Operating out of a system database just seemed to make more logical sense since the whole point is to gather data from 'user' databases.)

Essentially, what this is doing is generating the following statement for each database:

SELECT '[db_name].[dbo].' + name AS [procedure_name]
FROM sys.procedures 
WHERE schema_id =  
 (SELECT schema_id 
  FROM sys.schemas
  WHERE name = 'dbo')

and then UNION-ing all the results together. It involves a little 'magic' in that @sql = @sql + (stuff) statement, which basically makes it so each row emitted by the SELECT statement adds to the value of @sql (which is why it must be pre-populated as '' instead of NULL.)

I want to emphasize that dynamic SQL like this is risky - like any other kind of SQL statement building, it opens you up to SQL injection attacks, even if contained in a stored procedure. As a script that you store on your machine and run ad hoc though I think it's a good solution.