While executing below update_statistics script on SQL 2008 R2 i'm getting table captured

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » While executing below update_statistics...

While executing below update_statistics script on SQL 2008 R2 i'm getting table captured_columns does not exist.

Rate Topic

Display Mode

Topic Options

Author

Message

prodyut.dasgupta

Posted Monday, December 24, 2012 1:51 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 3:23 AM
Points: 17, Visits: 63

--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*/

Post #1399841

prodyut.dasgupta

Posted Monday, December 24, 2012 3:58 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 3:23 AM
Points: 17, Visits: 63

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*/

Post #1399859

opc.three

Posted Monday, December 24, 2012 11:58 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826, Visits: 11,951

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1399950

« Prev Topic | Next Topic »

Permissions