SQL Server doesn't aggregate this type of information for you in any way, so you're going to use some kind of loop to get this information in a single resultset. One way is to concatenate a set of UNION ALL queries, but I think an explicit cursor populating a #temp table one row at a time is an easier way to digest what is happening, especially as the source query gets more complex:
First, create a #temp table:
CREATE TABLE #objs ( db sysname PRIMARY KEY, functions int, procs int, triggers int, views int );
Then:
DECLARE @sql nvarchar(max) = N'SELECT DB_NAME(), SUM(CASE WHEN CHARINDEX(N''FUNCTION'', o.type_desc) > 0 THEN 1 ELSE 0 END) [functions], SUM(CASE WHEN CHARINDEX(N''PROCEDURE'', o.type_desc) > 0 THEN 1 ELSE 0 END) [procs], SUM(CASE WHEN CHARINDEX(N''TRIGGER'', o.type_desc) > 0 THEN 1 ELSE 0 END) [triggers], SUM(CASE WHEN CHARINDEX(N''VIEW'', o.type_desc) > 0 THEN 1 ELSE 0 END) [views] FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id;'; DECLARE @n sysname, @e nvarchar(512); DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT QUOTENAME(name) FROM sys.databases WHERE database_id > 4 AND state = 0 -- online ORDER BY name; OPEN c; FETCH NEXT FROM c INTO @n; WHILE @@FETCH_STATUS <> -1 BEGIN SET @e = @n + N'.sys.sp_executesql '; INSERT #objs EXEC @e @sql; FETCH NEXT FROM c INTO @n; END CLOSE c; DEALLOCATE c; SELECT * FROM #objs;
Note that for any database that doesn't have any of these objects you will get a row of NULL values instead of 0s. If you want to change that, just fix your pattern from:
SUM(CASE WHEN ... THEN 1 ELSE 0 END)
To:
COUNT(CASE WHEN ... THEN 1 END)
Clean up:
DROP TABLE #objs;
You can also take a look at sp_foreachdb.
For completeness, here is an example using dynamic SQL:
DECLARE @query nvarchar(max) = N' UNION ALL SELECT N''$dbs$'', SUM(CASE WHEN CHARINDEX(N''FUNCTION'', o.type_desc) > 0 THEN 1 ELSE 0 END) [functions], SUM(CASE WHEN CHARINDEX(N''PROCEDURE'', o.type_desc) > 0 THEN 1 ELSE 0 END) [procs], SUM(CASE WHEN CHARINDEX(N''TRIGGER'', o.type_desc) > 0 THEN 1 ELSE 0 END) [triggers], SUM(CASE WHEN CHARINDEX(N''VIEW'', o.type_desc) > 0 THEN 1 ELSE 0 END) [views] FROM $dbv$.sys.sql_modules m INNER JOIN $dbv$.sys.objects o ON m.object_id = o.object_id'; DECLARE @sql nvarchar(max) = N''; SELECT @sql += REPLACE(REPLACE(@query, N'$dbs$', REPLACE(name,'''','''''')), N'$dbv$',QUOTENAME(name)) FROM sys.databases WHERE database_id > 4 AND state = 0 -- online ORDER BY name; SET @sql = STUFF(@sql, 1, 12, N'') + N';'; EXEC sys.sp_executesql @sql;
