SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Monitor object usage over time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tfountain
Constraint Violating Yak Guru

USA
491 Posts

Posted - 06/08/2004 :  10:26:49  Show Profile  Reply with Quote
Ok, we came up with this one so we can determine what objects (procedures, functions, views) become obsolete over time. This solution requires two procedures and one table. The first procedure (usp_dba_FindCachedObjects) is ran via a SQL Server job to capture everything in the cache at that point. We have it running every 5 minutes but the interval required is pretty much a case by case basis. The second procedure (usp_dba_GetStaleObjects) is ran when we want to generate a list of objects to review before obsoleting. Right now it only looks at objects not found at all (do not reside in ObjectExecution). However, with the column LastFoundTime we left room to modify the second procedure to query information based on dates as well.

Table Defintion:
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

Procedure to capture results:
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

Procedure to query for stale objects:
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

Edited by - tfountain on 06/14/2004 08:54:39
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000