SQL Server – Retrieve All Database Objects Owned by a Database

sql serversql-server-2008

Is there a way to search all database objects by a particular database name or its id. Is that possible? I would like to do this so that I can find out how many stored procedures, functions, views, etc on each DB. By googling, I found some posts about searching objects by user but not database.

Update:
To make my question little bit more clear, I already have this code to get total count of each object type for a specific DB.

SELECT
    SUM(CASE WHEN CHARINDEX('FUNCTION', o.type_desc) > 0 THEN 1 ELSE 0 END) [functions],
    SUM(CASE WHEN CHARINDEX('STORED_PROCEDURE', o.type_desc) > 0 THEN 1 ELSE 0 END) [procs],
    SUM(CASE WHEN CHARINDEX('TRIGGER', o.type_desc) > 0 THEN 1 ELSE 0 END) [triggers],
    SUM(CASE WHEN CHARINDEX('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

enter image description here

But I was wondering if I can iterate through sys.databases and return result like below:

enter image description here

Best Answer

Simple dynamic SQL should do the trick for this.

DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    + 'UNION ALL' + CHAR(10) END + N'SELECT DatabaseName = ''' + d.name + N'''
    , o.type_desc
    , COUNT(1)
FROM ' + QUOTENAME(d.name) + N'.sys.objects o
GROUP BY o.type_desc'
FROM sys.databases d
WHERE d.database_id > 4
    AND d.state = 0;

PRINT @cmd;
EXEC sys.sp_executesql @cmd;

On my system, I get this:

╔══════════════╦══════════════════════════════════╦══════════════════╗
║ DatabaseName ║            type_desc             ║ (No column name) ║
╠══════════════╬══════════════════════════════════╬══════════════════╣
║ master       ║ SYSTEM_TABLE                     ║               79 ║
║ master       ║ VIEW                             ║                1 ║
║ master       ║ SQL_STORED_PROCEDURE             ║               30 ║
║ master       ║ SERVICE_QUEUE                    ║                3 ║
║ master       ║ USER_TABLE                       ║                5 ║
║ master       ║ INTERNAL_TABLE                   ║               14 ║
║ master       ║ SQL_SCALAR_FUNCTION              ║                1 ║
║ tempdb       ║ SYSTEM_TABLE                     ║               72 ║
║ tempdb       ║ SERVICE_QUEUE                    ║                3 ║
║ tempdb       ║ USER_TABLE                       ║               41 ║
║ tempdb       ║ PRIMARY_KEY_CONSTRAINT           ║                5 ║
║ tempdb       ║ INTERNAL_TABLE                   ║               20 ║
║ model        ║ SYSTEM_TABLE                     ║               72 ║
║ model        ║ SERVICE_QUEUE                    ║                3 ║
║ model        ║ INTERNAL_TABLE                   ║               16 ║
║ msdb         ║ SYSTEM_TABLE                     ║               72 ║
║ msdb         ║ VIEW                             ║               79 ║
║ msdb         ║ SQL_TABLE_VALUED_FUNCTION        ║               13 ║
║ msdb         ║ DEFAULT_CONSTRAINT               ║              220 ║
║ msdb         ║ SQL_STORED_PROCEDURE             ║              475 ║
║ msdb         ║ SYNONYM                          ║               10 ║
║ msdb         ║ FOREIGN_KEY_CONSTRAINT           ║               60 ║
║ msdb         ║ SERVICE_QUEUE                    ║                6 ║
║ msdb         ║ SQL_INLINE_TABLE_VALUED_FUNCTION ║                8 ║
║ msdb         ║ CHECK_CONSTRAINT                 ║                8 ║
║ msdb         ║ USER_TABLE                       ║              144 ║
║ msdb         ║ PRIMARY_KEY_CONSTRAINT           ║               88 ║
║ msdb         ║ INTERNAL_TABLE                   ║               19 ║
║ msdb         ║ TYPE_TABLE                       ║                1 ║
║ msdb         ║ SQL_TRIGGER                      ║               38 ║
║ msdb         ║ SQL_SCALAR_FUNCTION              ║               37 ║
║ msdb         ║ UNIQUE_CONSTRAINT                ║               26 ║
║ db_admn      ║ SYSTEM_TABLE                     ║               72 ║
║ db_admn      ║ DEFAULT_CONSTRAINT               ║                1 ║
║ db_admn      ║ SQL_STORED_PROCEDURE             ║                1 ║
║ db_admn      ║ SERVICE_QUEUE                    ║                3 ║
║ db_admn      ║ USER_TABLE                       ║                8 ║
║ db_admn      ║ PRIMARY_KEY_CONSTRAINT           ║                8 ║
║ db_admn      ║ INTERNAL_TABLE                   ║               16 ║
╚══════════════╩══════════════════════════════════╩══════════════════╝

If you want to get funky, you might wrap the above dynamic SQL inside a PIVOT, like this:

DECLARE @cmd nvarchar(max);
DECLARE @types nvarchar(max);
SET @types = '[AGGREGATE_FUNCTION]
    , [CHECK_CONSTRAINT]
    , [CLR_SCALAR_FUNCTION]
    , [CLR_STORED_PROCEDURE]
    , [CLR_TABLE_VALUED_FUNCTION]
    , [CLR_TRIGGER]
    , [DEFAULT_CONSTRAINT]
    , [EXTENDED_STORED_PROCEDURE]
    , [FOREIGN_KEY_CONSTRAINT]
    , [INTERNAL_TABLE]
    , [PLAN_GUIDE]
    , [PRIMARY_KEY_CONSTRAINT]
    , [REPLICATION_FILTER_PROCEDURE]
    , [RULE]
    , [SEQUENCE_OBJECT]
    , [SERVICE_QUEUE]
    , [SQL_INLINE_TABLE_VALUED_FUNCTION]
    , [SQL_SCALAR_FUNCTION]
    , [SQL_STORED_PROCEDURE]
    , [SQL_TABLE_VALUED_FUNCTION]
    , [SQL_TRIGGER]
    , [SYNONYM]
    , [SYSTEM_TABLE]
    , [TABLE_TYPE]
    , [UNIQUE_CONSTRAINT]
    , [USER_TABLE]
    , [VIEW]';

SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    + 'UNION ALL' + CHAR(10) END + N'SELECT DatabaseName = ''' + d.name + N'''
    , o.type_desc
    , c = COUNT(1)
FROM ' + QUOTENAME(d.name) + N'.sys.objects o
GROUP BY o.type_desc'
FROM sys.databases d
WHERE d.database_id > 4
    AND d.state = 0;

SET @cmd = 'SELECT *
FROM (
' + @cmd + '
) src
PIVOT (
    MAX(c)
    FOR type_desc IN (' + @types + ')
) pvt'

SET @cmd = @cmd + CHAR(13) + CHAR(10) + 'ORDER BY DatabaseName;';
PRINT @cmd;
EXEC sys.sp_executesql @cmd;

Then you get a single row for each database, with the count of each type of object in a column for each type:

╔══════════════╦════════════════════╦══════════════════╦═════════════════════╦══════════════════════╦═══════════════════════════╦═════════════╦════════════════════╦═══════════════════════════╦════════════════════════╦════════════════╦════════════╦════════════════════════╦══════════════════════════════╦══════╦═════════════════╦═══════════════╦══════════════════════════════════╦═════════════════════╦══════════════════════╦═══════════════════════════╦═════════════╦═════════╦══════════════╦════════════╦═══════════════════╦════════════╦══════╗
║ DatabaseName ║ AGGREGATE_FUNCTION ║ CHECK_CONSTRAINT ║ CLR_SCALAR_FUNCTION ║ CLR_STORED_PROCEDURE ║ CLR_TABLE_VALUED_FUNCTION ║ CLR_TRIGGER ║ DEFAULT_CONSTRAINT ║ EXTENDED_STORED_PROCEDURE ║ FOREIGN_KEY_CONSTRAINT ║ INTERNAL_TABLE ║ PLAN_GUIDE ║ PRIMARY_KEY_CONSTRAINT ║ REPLICATION_FILTER_PROCEDURE ║ RULE ║ SEQUENCE_OBJECT ║ SERVICE_QUEUE ║ SQL_INLINE_TABLE_VALUED_FUNCTION ║ SQL_SCALAR_FUNCTION ║ SQL_STORED_PROCEDURE ║ SQL_TABLE_VALUED_FUNCTION ║ SQL_TRIGGER ║ SYNONYM ║ SYSTEM_TABLE ║ TABLE_TYPE ║ UNIQUE_CONSTRAINT ║ USER_TABLE ║ VIEW ║
╠══════════════╬════════════════════╬══════════════════╬═════════════════════╬══════════════════════╬═══════════════════════════╬═════════════╬════════════════════╬═══════════════════════════╬════════════════════════╬════════════════╬════════════╬════════════════════════╬══════════════════════════════╬══════╬═════════════════╬═══════════════╬══════════════════════════════════╬═════════════════════╬══════════════════════╬═══════════════════════════╬═════════════╬═════════╬══════════════╬════════════╬═══════════════════╬════════════╬══════╣
║ db_admn      ║ NULL               ║ NULL             ║ NULL                ║ NULL                 ║ NULL                      ║ NULL        ║ 1                  ║ NULL                      ║ NULL                   ║             16 ║ NULL       ║ 8                      ║ NULL                         ║ NULL ║ NULL            ║             3 ║ NULL                             ║ NULL                ║ 1                    ║ NULL                      ║ NULL        ║ NULL    ║           72 ║ NULL       ║ NULL              ║ 8          ║ NULL ║
║ master       ║ NULL               ║ NULL             ║ NULL                ║ NULL                 ║ NULL                      ║ NULL        ║ NULL               ║ NULL                      ║ NULL                   ║             14 ║ NULL       ║ NULL                   ║ NULL                         ║ NULL ║ NULL            ║             3 ║ NULL                             ║ 1                   ║ 30                   ║ NULL                      ║ NULL        ║ NULL    ║           79 ║ NULL       ║ NULL              ║ 5          ║ 1    ║
║ model        ║ NULL               ║ NULL             ║ NULL                ║ NULL                 ║ NULL                      ║ NULL        ║ NULL               ║ NULL                      ║ NULL                   ║             16 ║ NULL       ║ NULL                   ║ NULL                         ║ NULL ║ NULL            ║             3 ║ NULL                             ║ NULL                ║ NULL                 ║ NULL                      ║ NULL        ║ NULL    ║           72 ║ NULL       ║ NULL              ║ NULL       ║ NULL ║
║ msdb         ║ NULL               ║ 8                ║ NULL                ║ NULL                 ║ NULL                      ║ NULL        ║ 220                ║ NULL                      ║ 60                     ║             19 ║ NULL       ║ 88                     ║ NULL                         ║ NULL ║ NULL            ║             6 ║ 8                                ║ 37                  ║ 475                  ║ 13                        ║ 38          ║ 10      ║           72 ║ NULL       ║ 26                ║ 144        ║ 79   ║
║ tempdb       ║ NULL               ║ NULL             ║ NULL                ║ NULL                 ║ NULL                      ║ NULL        ║ NULL               ║ NULL                      ║ NULL                   ║             20 ║ NULL       ║ 5                      ║ NULL                         ║ NULL ║ NULL            ║             3 ║ NULL                             ║ NULL                ║ NULL                 ║ NULL                      ║ NULL        ║ NULL    ║           72 ║ NULL       ║ NULL              ║ 41         ║ NULL ║
╚══════════════╩════════════════════╩══════════════════╩═════════════════════╩══════════════════════╩═══════════════════════════╩═════════════╩════════════════════╩═══════════════════════════╩════════════════════════╩════════════════╩════════════╩════════════════════════╩══════════════════════════════╩══════╩═════════════════╩═══════════════╩══════════════════════════════════╩═════════════════════╩══════════════════════╩═══════════════════════════╩═════════════╩═════════╩══════════════╩════════════╩═══════════════════╩════════════╩══════╝
Related Question