December 24, 2012 at 1:51 am
--DROP TABLE STATS_TAB
--drop proc UPD_STATISTICS
--Syntax for Usage
--exec upd_statistics 'PUBS' OR
--exec upd_statistics 'PUBS'
ALTER PROC UPD_STATISTICS (@DBNAME VARCHAR(100) = 'A' ) AS
/* AUTHOR : Rajesh S. Chandan
CREATED DATE : March 19,2003 at 18:34
UPDATED DATE :
VERSION : 1.01*/
/*------------------- Declaration part -------------------- */
IF NOT EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME = 'STATS_TAB')
CREATE TABLE STATS_TAB
(
DBNAME VARCHAR(100) NULL,
INAME VARCHAR(100) NULL,
ONAME VARCHAR(100) NULL,
Type VARCHAR(1) NULL,
SDATE DATETIME NULL
)
Declare @Var_c2 varchar(1000),
@objName varchar(256),
@OName varchar(256),
@SDate datetime,
@r int,
@Check_DB1 varchar(500),
@L INT,
@Check_ALLOC varchar(500),
@Check_CHKCAT varchar(500),
@Check_Shrink varchar(500),
@Iname varchar(50),
@Type varchar(50),
@StatDate datetime,
@VarCur varchar(500)
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT cursor_name FROM master..syscursors WHERE UPPER(cursor_name) = 'C1')
BEGIN
CLOSE C1
Deallocate C1
END
--Declare @dbname varchar(250)
CREATE TABLE #STATTAB
(
OBJ_NAME VARCHAR(100) NULL,
TAB_NAME VARCHAR(100) NULL,
TYPE CHAR(1) NULL,
STAT_DATE DATETIME NULL
)
CREATE TABLE #DBLIST (DB_NAME VARCHAR(100))
IF @DBNAME = 'A' /*If executing procedures without specifying Database Name it will execute
for all Databases*/
BEGIN
INSERT INTO #DBLIST(DB_NAME) select name from master..sysdatabases where Upper(name) NOT IN ('MASTER','NORTHWIND','PUBS', 'TEMPDB','MSDB' ,'MODEL' ,'DISTRIBUTION', 'TESTDB')
END
ELSE
BEGIN
INSERT INTO #DBLIST(DB_NAME) VALUES (@DBNAME)
END
Declare c1 cursor for select db_name from #DBLIST
open c1
fetch next from c1 into @dbname
Set @VarCur = 'IF NOT EXISTS(SELECT NAME FROM '+@dbname+'..SYSOBJECTS WHERE NAME =''STATS_TAB'')
begin
CREATE TABLE '+@dbname+'..STATS_TAB
(
DBNAME varchar(100) null,
OBJ_NAME VARCHAR(100) NULL,
TAB_NAME VARCHAR(100) NULL,
TYPE CHAR(1) NULL,
STAT_DATE DATETIME NULL
)
end
'
--print @VarCur
Execute (@VarCur)
while @@fetch_status=0
begin
Set @VarCur = 'Declare c2 cursor for select
name FROM '+@dbname+'..sysobjects where type=''U'' order by name'
--print @VarCur
Exec(@VarCur)
open c2
fetch next from c2 into @objName
while @@fetch_status =0
Begin
Set @Var_c2= 'Update Statistics '+@dbname+'..['+@objname+']'
Exec (@Var_c2)
print @var_c2
fetch next from c2 into @objName
End
close c2
deallocate c2
fetch next from c1 into @dbname
end
close c1
deallocate c1
Set @VarCur = 'Select i.name, o.name,o.type,
STATS_DATE(i.id, i.indid) FROM '+@dbname+'..sysobjects o,'+@dbname+'..sysindexes i WHERE o.id = i.id and o.type=''U''
and indid>=1 and indid<255 order by o.name'
--print @varcur
Insert into #STATTAB Exec(@VarCur)
Declare c3 cursor for Select * from #STATTAB
open c3
fetch next from c3 into @IName, @OName, @Type, @SDate
while @@fetch_status =0
begin
INSERT INTO STATS_TAB values (@dbname,@IName, @OName, @Type, @SDate)
fetch next from c3 into @IName, @OName, @Type, @SDate
End
print 'Statistic Information stored into STATS_TAB table for reference'
print ' select * from STATS_TAB '
print ' NULL Values for StatsDate indicate no rows in the table'
DROP TABLE #DBLIST
set nocount off /*Reset nocount*/
END /*End of Proc*/
December 24, 2012 at 3:58 am
While executing following update statistics script on sql 2008 i'm getting error (Msg 2706, Level 16, State 6, Line 1
Table 'captured_columns' does not exist).However the script is working fine on SQL 2005.
I have checked 'captured_columns' table is already exist in sys.sysobjects.Can any one help to resolve this issue
--DROP TABLE STATS_TAB
--drop proc UPD_STATISTICS
--Syntax for Usage
--exec upd_statistics 'PUBS' OR
--exec upd_statistics 'PUBS'
ALTER PROC UPD_STATISTICS (@DBNAME VARCHAR(100) = 'A' ) AS
/*------------------- Declaration part -------------------- */
IF NOT EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME = 'STATS_TAB')
CREATE TABLE STATS_TAB
(
DBNAME VARCHAR(100) NULL,
INAME VARCHAR(100) NULL,
ONAME VARCHAR(100) NULL,
Type VARCHAR(1) NULL,
SDATE DATETIME NULL
)
Declare @Var_c2 varchar(1000),
@objName varchar(256),
@OName varchar(256),
@SDate datetime,
@r int,
@Check_DB1 varchar(500),
@L INT,
@Check_ALLOC varchar(500),
@Check_CHKCAT varchar(500),
@Check_Shrink varchar(500),
@Iname varchar(50),
@Type varchar(50),
@StatDate datetime,
@VarCur varchar(500)
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT cursor_name FROM master..syscursors WHERE UPPER(cursor_name) = 'C1')
BEGIN
CLOSE C1
Deallocate C1
END
--Declare @dbname varchar(250)
CREATE TABLE #STATTAB
(
OBJ_NAME VARCHAR(100) NULL,
TAB_NAME VARCHAR(100) NULL,
TYPE CHAR(1) NULL,
STAT_DATE DATETIME NULL
)
CREATE TABLE #DBLIST (DB_NAME VARCHAR(100))
IF @DBNAME = 'A' /*If executing procedures without specifying Database Name it will execute
for all Databases*/
BEGIN
INSERT INTO #DBLIST(DB_NAME) select name from master..sysdatabases where Upper(name) NOT IN ('MASTER','NORTHWIND','PUBS', 'TEMPDB','MSDB' ,'MODEL' ,'DISTRIBUTION', 'TESTDB')
END
ELSE
BEGIN
INSERT INTO #DBLIST(DB_NAME) VALUES (@DBNAME)
END
Declare c1 cursor for select db_name from #DBLIST
open c1
fetch next from c1 into @dbname
Set @VarCur = 'IF NOT EXISTS(SELECT NAME FROM '+@dbname+'..SYSOBJECTS WHERE NAME =''STATS_TAB'')
begin
CREATE TABLE '+@dbname+'..STATS_TAB
(
DBNAME varchar(100) null,
OBJ_NAME VARCHAR(100) NULL,
TAB_NAME VARCHAR(100) NULL,
TYPE CHAR(1) NULL,
STAT_DATE DATETIME NULL
)
end
'
--print @VarCur
Execute (@VarCur)
while @@fetch_status=0
begin
Set @VarCur = 'Declare c2 cursor for select
name FROM '+@dbname+'..sysobjects where type=''U'' order by name'
--print @VarCur
Exec(@VarCur)
open c2
fetch next from c2 into @objName
while @@fetch_status =0
Begin
Set @Var_c2= 'Update Statistics '+@dbname+'..['+@objname+']'
Exec (@Var_c2)
print @var_c2
fetch next from c2 into @objName
End
close c2
deallocate c2
fetch next from c1 into @dbname
end
close c1
deallocate c1
Set @VarCur = 'Select i.name, o.name,o.type,
STATS_DATE(i.id, i.indid) FROM '+@dbname+'..sysobjects o,'+@dbname+'..sysindexes i WHERE o.id = i.id and o.type=''U''
and indid>=0 and indid<255 and id>1000 order by o.name'
--print @varcur
Insert into #STATTAB Exec(@VarCur)
Declare c3 cursor for Select * from #STATTAB
open c3
fetch next from c3 into @IName, @OName, @Type, @SDate
while @@fetch_status =0
begin
INSERT INTO STATS_TAB values (@dbname,@IName, @OName, @Type, @SDate)
fetch next from c3 into @IName, @OName, @Type, @SDate
End
print 'Statistic Information stored into STATS_TAB table for reference'
print ' select * from STATS_TAB '
print ' NULL Values for StatsDate indicate no rows in the table'
DROP TABLE #DBLIST
set nocount off /*Reset nocount*/
END /*End of Proc*/
December 24, 2012 at 11:58 am
Tracing the nested cursors in that process made my head hurt a little. If you want to stick with a manual stats update process in T-SQL I would recommend you look into the two procs master.sys.sp_MSforeachdb and master.sys.sp_MSforeachtable. They are undocumented but are commonly used so might as well be documented and supported. Granted, they make use of cursors but allow for a much more expressive syntax than what you are using. You could literally boil your process down to one call to master.sys.sp_MSforeachdb that makes a call to master.sys.sp_MSforeachtable using these built-in procs.
If you want to stick with your script then please update the use of the backward compatibility views with the new catalog views (e.g. replace sysobjects with sys.objects).
If neither of those are appealing you could look into use a Maintenance Plan as it includes a task for updating stats. Update Statistics Task (Maintenance Plan) - SQL Server 2008 R2
Or look into using Ola Hallengren's solution which is complete with index maintenance and is very well tested by the community. SQL Server Index and Statistics Maintenance
edit: spelling
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply