November 8, 2006 at 3:17 pm
I'm writing a SP to keep track of and update statistics on databases in our SQL Server 2000 environment. The stored procedure will execute from a "common" database (DBA) so I've written the SP to accept a database name (and other parameters).
Everything works fine until I try to execute the STATS_DATE function (which takes a table id and an index id as parameters). Apparently I can't qualifiy the parameters... :
select stats_date(object_id('mydatabase.dbo.sysobjects.id'), ...)
So... if there's not a way to do this through the system function is there another way to get the information?
Thanks!
Glenn
November 8, 2006 at 5:01 pm
System functions are local to the scope. You need to "get into that database shoes"
USE DBA
GO
CREATE PROC dbo.GetStat @ObjectID int, @IndId smallint
AS
select stats_date(@ObjectID, @IndId )
GO
EXEC('use mydatabase
EXEC DBA.dbo.GetStat Object_ID(''dbo.Table'', 1)
')
_____________
Code for TallyGenerator
November 9, 2006 at 9:03 am
Serqiy -
I see where you're going with this but given the scope constraints I don't see how this will work. The GetStat SP still executes in the scope of the DBA database (not the target database). I've incorporated your suggestion into the following code snippet. The results still yield null values for the stat_date resultset:
DBA | 1800-0101 00:00:00.000
I don't want to embed DBA/ADMIN SP into the individual databases and/or master DB.
So... if I can't get the stat_date to work - is there another way to get the table statistics last update datetime?
Thanks!
Glenn
CREATE PROCEDURE DBA_GetTableStats
@TableId int,
@IndexId smallint
AS
SELECT db_name() as DB, IsNull(stats_date(@TableId, @IndexId), '01-01-1800')
GO
DECLARE @dbname varchar(50)
SET @dbname = 'targetDB'
CREATE TABLE #STATS (
name varchar(100),
tableid int,
[index] varchar(100),
indexid smallint,
rowmodctr int,
statdate datetime)
INSERT #stats EXEC ('
select
obj.name,
obj.id as TableId,
idx.name,
idx.indid as IndexId,
idx.rowmodctr,
NULL
from
' + @dbname + '.dbo.sysobjects obj inner join
' + @dbname + '.dbo.sysindexes idx on obj.id = idx.id
where
obj.type="U" and
idx.indid between 1 and 254 and
idx.rowmodctr>=
case
when idx.rows <=500 then 500
else (idx.rows*0.2)+500
end')
DECLARE @tblid int
DECLARE @idxid smallint
DECLARE @statdate datetime
DECLARE cur CURSOR FOR Select tableid, indexid from #stats
OPEN cur
FETCH NEXT FROM cur INTO @tblid, @idxid
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('USE ' + @dbname + ' EXEC dba.dbo.dba_GetTableStats @tableid=' + @tblid + ' , @indexid=' + @idxid + '')
UPDATE #stats SET statdate = @statdate -- to be replaced with EXEC results (when it's working!!!)
WHERE tableid = @tblid and indexid = @idxid
FETCH NEXT FROM cur INTO @tblid, @idxid
END
CLOSE cur
DEALLOCATE cur
select * from #stats
drop table #stats
November 9, 2006 at 12:11 pm
If you pay close attention at what sergy posted you'll realize that there is a "use" statement *in* the dynamic sql
exec (' use ; do your stuff') -- back to current db context.
Cheers,
* Noel
November 9, 2006 at 12:17 pm
If you'll notice - I incorporated the Use statment into the test code... It does not cause the stats_date function to execute in the target database context but rather in the DBA database context.
Everything work fine provided that I execute within the target context - but that doesn't provide the flexibility that I'm after.
I'm going to look into using OSQL with an execution script.
Glenn
November 9, 2006 at 12:25 pm
Well I am not sure then what is "your" issue, because
exec (' USE dba_data
SELECT object_name(i.id)table_name, rows, rowmodctr,''Index Name'' = i.name, ''Statistics Date'' = STATS_DATE(i.id, i.indid)
FROM sysindexes i
WHERE i.name not like''sys%''')
WORKS 100% of the time for me *from any database* and it returns *correct results*
Cheers,
* Noel
November 9, 2006 at 12:29 pm
Noel -
Thanks! That's what I've been trying to do - just can't seem to get it to work with the EXEC...
Glenn
August 11, 2009 at 9:26 am
noeld (11/9/2006)
exec (' USE dba_data
SELECT object_name(i.id)table_name, rows, rowmodctr,''Index Name'' = i.name, ''Statistics Date'' = STATS_DATE(i.id, i.indid)
FROM sysindexes i
WHERE i.name not like''sys%''')
WORKS 100% of the time for me *from any database* and it returns *correct results*
Well, I am certainly late to the game seeing as it has been almost 3 years since the last post here but I came across this thread looking for some of the same info and thought I could at least help with the discrepancy noted above.
The reason that Noel's SQL works and that Sergiy's Proc does not is that Noel is using a USE statement IN the SQL that also executes STATS_DATE whereas the Proc listed above by Sergiy simply calls STATS_DATE in the Proc and does not change the local context INSIDE the Proc. Hence, the USE statement issued by Sergiy in the EXEC at the end of the example really does not change the context of where STATS_DATE is running from. The only way to do that is to call EXEC within the Proc and in doing so it would then look more like what Noel posted. Here is something that does work since it takes a parameter for the DBName so that it can be used INSIDE the Proc to change the context inside an EXEC that will call STATS_DATE:
USE [Common]
GO
CREATE PROCEDURE dbo.GetStats (
@DBName SYSNAME,
@ObjectID INT,
@StatsID INT
)
AS
SET NOCOUNT ON
DECLARE @StatsSQL NVARCHAR(500)
SET @StatsSQL =
N'USE [' +
@DBName +
N']; SELECT STATS_DATE(' +
CONVERT(NVARCHAR(10), @ObjectID) +
N', ' +
CONVERT(NVARCHAR(10), @StatsID) +
N');'
EXEC (@StatsSQL)
GO
And then it would be called from any DB as follows:
EXEC Common.dbo.GetStats 'Test', 1463012293, 1
Now, what I would REALLY love to know is:
What is the underlying meta-data table (or DMV) that this date comes from so I can query it directly?
Take care,
Solomon Rutzky...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply