July 30, 2009 at 5:55 am
:hehe: 😎 :w00t:
The case: A developer came to me and asked about finding out the last time a particular stored procedure was run. 2005 is good with those questions, but not so much in 2000. I did research all over the place, but could find any solution. Some articles said to use profiler, some other said that cannot be done unless a whole script is developed, but not of those really address the how.
The question: Does anyone knows how in 2000? If a stored procedure is call by a DTS package or a job, that can be included in the job, but this particular developer did not have any logging in his job, so it is not an easy task. Maybe some one has a clue.
Thank you :w00t::w00t:
July 30, 2009 at 10:34 am
This is from another forum. I used this many times in the past to capture last executed time of stored procedures. It uses the syscacheobjects table.
---------------------------------------
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
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
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 ()
--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 .dbo.ObjectExecution (DatabaseID, ObjectName, LastFoundTime)
SELECT
TC.DatabaseID,
TC.ObjectName,
@CurrentDateTime
FROM #TempCache AS TC
LEFT JOIN .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 .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
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 ()
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 .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
July 31, 2009 at 7:13 pm
Excellent Alex.
Many thanks for sharing the same!!!!
MJ
August 5, 2009 at 6:17 am
Many, many, many thanks. It did work as needed.:w00t: :hehe:
August 15, 2009 at 6:27 am
Hey thanks Dude.
I've to try this but if this works this can be tweaked to use for many other things also. 😉
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply