2

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

1

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 ║
╚══════════════╩════════════════════╩══════════════════╩═════════════════════╩══════════════════════╩═══════════════════════════╩═════════════╩════════════════════╩═══════════════════════════╩════════════════════════╩════════════════╩════════════╩════════════════════════╩══════════════════════════════╩══════╩═════════════════╩═══════════════╩══════════════════════════════════╩═════════════════════╩══════════════════════╩═══════════════════════════╩═════════════╩═════════╩══════════════╩════════════╩═══════════════════╩════════════╩══════╝
3

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;
1
SELECT *
FROM   [Database].sys.objects

Will list all the objects in a database. You can filter on type to remove Keys and other things you don't want.

Like this for example will remove Keys and Constraints from the list

SELECT *
FROM   [Database].sys.objects
WHERE  type NOT IN ('PK','F','D','C','UQ')

Using this system table you can search on Name, Id, Schema, ect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.