Home
/
SQL

SQL sp_procs_in_cache

sp_procs_in_cache

set quoted_identifier off
go
drop proc sp_procs_in_cache
go
CREATE PROCEDURE sp_procs_in_cache
@name  varchar(30) = NULL
AS
SET NOCOUNT ON
IF @name IS NULL  SET @name = db_name()
IF @name = 'ALL' BEGIN
DECLARE dbnames  CURSOR LOCAL FOR SELECT name FROM master..sysdatabases
OPEN dbnames
FETCH dbnames into @name
   WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'Database: '  + @name
    exec ("SELECT 'Procedure Name' = convert(varchar(30), o.name), 
                    'Plan Type ' = convert(varchar(30), cacheobjtype), Plans = count(*)
    FROM master..syscacheobjects c, " + @name + "..sysobjects o
    WHERE c.objid = o.id
    AND objtype = 'Proc'
   -- AND cacheobjtype = 'Executable Plan' 
    AND  dbid = db_id('" + @name + "')
    GROUP BY  o.name, cacheobjtype
    ORDER BY  o.name, cacheobjtype")

    FETCH dbnames into @name
   END /* fetch loop */
   DEALLOCATE dbnames
END /* IF no parameter */
ELSE BEGIN
    PRINT 'Database : '  + @name
    exec ("SELECT  'Procedure Name' = convert(varchar(30), o.name), 
                    'Plan Type ' = convert(varchar(30), cacheobjtype), Plans = count(*)
    FROM master..syscacheobjects c, " + @name + "..sysobjects o
    WHERE c.objid = o.id
    AND objtype = 'Proc'
    --AND cacheobjtype = 'Executable Plan' 
    AND  dbid = db_id('" + @name + "')
    GROUP BY  o.name, cacheobjtype
    ORDER BY  o.name, cacheobjtype")
END /* ELSE just one database */
RETURN

Related Snippets