USE <database of your choice>
GO

IF OBJECT_ID('dbo.ObjectExecution') IS NULL
    BEGIN
        CREATE TABLE dbo.ObjectExecution
        (
            InternalGUID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_ObjectExecution_InternalGUID DEFAULT NEWID(),
            DatabaseID INT NOT NULL,
            ObjectName SYSNAME NOT NULL,
            LastFoundTime DATETIME NOT NULL CONSTRAINT DF_ObjectExecution_LastFoundTime DEFAULT GETDATE(),
            UserAdded NVARCHAR (20) NOT NULL CONSTRAINT DF_ObjectExecution_UserAdded DEFAULT (LEFT(SUSER_SNAME(), 20)),
            DateAdded DATETIME NOT NULL CONSTRAINT DF_ObjectExecution_DateAdded DEFAULT (GETDATE()),
            HostAdded NVARCHAR (20) NOT NULL CONSTRAINT DF_ObjectExecution_HostAdded DEFAULT (LEFT(HOST_NAME(), 20)),
            UserModified NVARCHAR (20) NOT NULL CONSTRAINT DF_ObjectExecution_UserModified DEFAULT (LEFT(SUSER_SNAME(), 20)),
            DateModified DATETIME NOT NULL CONSTRAINT DF_ObjectExecution_DateModified DEFAULT (GETDATE()),
            HostModified NVARCHAR (20) NOT NULL CONSTRAINT DF_ObjectExecution_HostModified DEFAULT (LEFT(HOST_NAME(), 20)),
            ConcurrencyStamp TIMESTAMP NULL,

            CONSTRAINT PK_ObjectExecution PRIMARY KEY NONCLUSTERED (DatabaseID, ObjectName),
            CONSTRAINT IX_ObjectExecution_InternalGUID UNIQUE CLUSTERED (InternalGUID),
            CONSTRAINT CK_ObjectExecution_DatabaseID CHECK (DatabaseID <> 0),
            CONSTRAINT CK_ObjectExecution_ObjectName CHECK (LEN(ObjectName) <> 0)
        )
    END

USE <database of your choice>
GO

IF OBJECT_ID('dbo.usp_dba_FindCachedObjects') IS NOT NULL
	DROP PROCEDURE dbo.usp_dba_FindCachedObjects
GO

------------------------------------------------------------------------------------
--This procedure will interrogate the master.dbo.SYSCACHEOBJECTS for any non system
--procedures, views and functions and insert or update the table ObjectExecution.
------------------------------------------------------------------------------------
--03/01/2004 TAF Created.
------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.usp_dba_FindCachedObjects
AS

SET NOCOUNT ON

--Temp table to store contents of cache (filtered for only what we need).
CREATE TABLE #TempCache
(
    DatabaseID INT NOT NULL,
    ObjectID INT NOT NULL,
    ObjectName NVARCHAR(3900) NOT NULL
)
--Variables to hold the cache variables while processing.
DECLARE @TempCacheID UNIQUEIDENTIFIER
DECLARE @DatabaseID INT

--Variables to execute dynamic SQL so we can change the database.
DECLARE @SQLString NVARCHAR(4000)
DECLARE @SQLParam NVARCHAR(4000)

--To hold time.
DECLARE @CurrentDateTime DATETIME

--Standard variables.
DECLARE @BeginTranCount INT
DECLARE @SavedError INT
DECLARE @SavedRowCount INT
DECLARE @ReturnCode INT

--Capture beginning transaction count for error handling
SET @BeginTranCount = @@TRANCOUNT
IF @BeginTranCount = 0 BEGIN TRANSACTION

--Get the current date and time.
SET @CurrentDateTime = GETDATE()

INSERT INTO #TempCache (DatabaseID, ObjectID, ObjectName)
SELECT
    DISTINCT dbid, objid, SQL
FROM master.dbo.SYSCACHEOBJECTS
WHERE objtype IN ('PROC', 'VIEW')
AND DB_NAME(dbid) IN (<provide database list>)

--Create a cursor so we can cycle our temp table and remove any records that are system objects.
DECLARE curDatabaseID CURSOR LOCAL FAST_FORWARD
FOR
SELECT DISTINCT
    DatabaseID
FROM #TempCache

OPEN curDatabaseID
FETCH curDatabaseID INTO @DatabaseID

WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @SQLString =
            'USE ' + DB_NAME(@DatabaseID) + CHAR(13) + CHAR(10) +
            'DELETE #TempCache' + CHAR(13) + CHAR(10) +
            'WHERE DatabaseID = @DatabaseID' + CHAR(13) + CHAR(10) +
            'AND OBJECTPROPERTY(ObjectID, ''IsMSShipped'') = 1'
        SET @SQLParam = '@DatabaseID INT'

        EXEC @ReturnCode = sp_executesql @stmt=@SQLString, @params=@SQLParam, @DatabaseID=@DatabaseID
        SET @SavedError = CASE @ReturnCode WHEN 0 THEN @@ERROR ELSE @ReturnCode END
        IF @SavedError <> 0 GOTO ErrorHandler        

        FETCH curDatabaseID INTO @DatabaseID
    END

CLOSE curDatabaseID
DEALLOCATE curDatabaseID

--Insert any records that do not already exist.
INSERT INTO <your database>.dbo.ObjectExecution (DatabaseID, ObjectName, LastFoundTime)
SELECT
    TC.DatabaseID,
    TC.ObjectName,
    @CurrentDateTime
FROM #TempCache AS TC
    LEFT JOIN <your database>.dbo.ObjectExecution AS OE
        ON TC.ObjectName = OE.ObjectName
WHERE OE.InternalGUID IS NULL

SELECT @SavedError = @@ERROR, @SavedRowCount = @@ROWCOUNT
IF @SavedError <> 0 GOTO ErrorHandler

--Update any records that already exist.
UPDATE OE
SET LastFoundTime = @CurrentDateTime
FROM #TempCache AS TC
    INNER JOIN <your database>.dbo.ObjectExecution AS OE
        ON TC.ObjectName = OE.ObjectName

SELECT @SavedError = @@ERROR, @SavedRowCount = @@ROWCOUNT
IF @SavedError <> 0 GOTO ErrorHandler

DROP TABLE #TempCache

--Standard transaction check and error handler.
IF @@TRANCOUNT > @BeginTranCount
    COMMIT TRANSACTION
RETURN 0

ErrorHandler:
    IF @@TRANCOUNT > @BeginTranCount
        ROLLBACK TRANSACTION

    RETURN ISNULL(@SavedError, -1)
GO

GRANT ALL ON dbo.usp_dba_FindCachedObjects TO PUBLIC
GO

USE <database of your choice>
GO

IF OBJECT_ID('dbo.usp_dba_GetStaleObjects') IS NOT NULL
	DROP PROCEDURE dbo.usp_dba_GetStaleObjects
GO

------------------------------------------------------------------------------------
--This procedure will retrieve any objects from the SYSOBJECTS tables that does not
--reside in the dbo.ObjectExecution table.  This will go across specific
--databases.
------------------------------------------------------------------------------------
--03/01/2004 TAF Created.
------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.usp_dba_GetStaleObjects
AS

SET NOCOUNT ON

--Variables to execute dynamic SQL so we can change the database.
DECLARE @SQLString NVARCHAR(4000)
DECLARE @SQLParam NVARCHAR(4000)

--Procedure specific variables.
DECLARE @DatabaseID INT

--Standard variables.
DECLARE @BeginTranCount INT
DECLARE @SavedError INT
DECLARE @SavedRowCount INT
DECLARE @ReturnCode INT

--Capture beginning transaction count for error handling
SET @BeginTranCount = @@TRANCOUNT
IF @BeginTranCount = 0 BEGIN TRANSACTION

--Create a temp table.
CREATE TABLE #TempSource
(
    DatabaseID INT NOT NULL,
    OwnerName SYSNAME NOT NULL,
    ObjectName NVARCHAR(3900) NOT NULL
)

DECLARE curDatabaseID CURSOR LOCAL FAST_FORWARD
FOR
SELECT
    dbid
FROM master.dbo.SYSDATABASES
WHERE Name IN (<provide database list>)

OPEN curDatabaseID
FETCH curDatabaseID INTO @DatabaseID

WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @SQLString = 
            'USE ' + DB_NAME(@DatabaseID) + CHAR(13) + CHAR(10) +
            'INSERT INTO #TempSource (DatabaseID, OwnerName, ObjectName)' + CHAR(13) + CHAR(10) +
            'SELECT' + CHAR(13) + CHAR(10) +
            '    @DatabaseID,' + CHAR(13) + CHAR(10) +
            '    SU.Name,' + CHAR(13) + CHAR(10) +
            '    SO.Name' + CHAR(13) + CHAR(10) +
            'FROM ' + DB_NAME(@DatabaseID) + '.dbo.SYSOBJECTS AS SO' + CHAR(13) + CHAR(10) +
            '    INNER JOIN ' + DB_NAME(@DatabaseID) + '.dbo.SYSUSERS AS SU' + CHAR(13) + CHAR(10) +
            '        ON SO.uid = SU.uid' + CHAR(13) + CHAR(10) +
            '    LEFT JOIN <your database>.dbo.ObjectExecution AS OE' + CHAR(13) + CHAR(10) +
            '        ON SO.Name = OE.ObjectName' + CHAR(13) + CHAR(10) +
            '        AND OE.DatabaseID = @DatabaseID' + CHAR(13) + CHAR(10) +
            'WHERE SO.XType IN (''P'', ''FN'', ''V'')' + CHAR(13) + CHAR(10) +
            'AND OBJECTPROPERTY(SO.ID, ''IsMSShipped'') = 0' + CHAR(13) + CHAR(10) +
            'AND SUBSTRING(SO.Name, 1, 1) <> ''_''' + CHAR(13) + CHAR(10) +
            'AND OE.InternalGUID IS NULL'
        SET @SQLParam = '@DatabaseID INT'

        EXEC @ReturnCode = sp_executesql @stmt=@SQLString, @params=@SQLParam, @DatabaseID=@DatabaseID
        SET @SavedError = CASE @ReturnCode WHEN 0 THEN @@ERROR ELSE @ReturnCode END
        IF @SavedError <> 0 GOTO ErrorHandler
        
        FETCH curDatabaseID INTO @DatabaseID
    END

CLOSE curDatabaseID
DEALLOCATE curDatabaseID

SELECT
    DB_NAME(DatabaseID) AS DatabaseName,
    OwnerName,
    ObjectName
FROM #TempSource
ORDER BY DB_NAME(DatabaseID), OwnerName, ObjectName

DROP TABLE #TempSource

--Standard transaction check and error handler.
IF @@TRANCOUNT > @BeginTranCount
    COMMIT TRANSACTION
RETURN 0

ErrorHandler:
    IF @@TRANCOUNT > @BeginTranCount
        ROLLBACK TRANSACTION

    RETURN ISNULL(@SavedError, -1)
GO

GRANT ALL ON dbo.usp_dba_GetStaleObjects TO PUBLIC
GO