April 7, 2015 at 11:34 am
Create Proc usp_updateStatsAllDbs
AS
BEGIN
SET ANSI_NULLS ON
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
--Set quoted_identifier off
DECLARE @tablename varchar(max)
DECLARE @tablename_header varchar(max)
DECLARE @dataname varchar(max)
DECLARE @dataname_header varchar(max)
DECLARE datanames_cursor CURSOR FOR
select name from sys.databases where database_id in (
SELECT dbid
FROM sys.sysdatabases
EXCEPT
SELECT database_id
FROM msdb..suspect_pages)
AND name NOT IN ('master','msdb','tempdb','model','distribution')
AND state_desc = 'ONLINE';
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
SELECT @dataname_header = 'Database ' + RTRIM(UPPER(@dataname))
PRINT ' '
PRINT @dataname_header
PRINT ' '
EXEC ('USE ' + @dataname + ' DECLARE tnames_cursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [' + @dataname + '].INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE''' )
Select @dataname_header = RTRIM(UPPER(@dataname))
Exec ('Use ' + @dataname)
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM tnames_cursor INTO @tablename
CONTINUE
END
SELECT @tablename_header = ' Updating ' + RTRIM(UPPER(@tablename))
PRINT ' '
PRINT @tablename_header
EXEC ('USE ' + @dataname + ' UPDATE STATISTICS ' + @tablename + 'WITH FULLSCAN, NORECOMPUTE')
FETCH NEXT FROM tnames_cursor INTO @tablename
END
--CLOSE tnames_cursor
DEALLOCATE tnames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
END
--CLOSE datanames_cursor
DEALLOCATE datanames_cursor
PRINT ' '
PRINT ' '
PRINT ' **** Update Stats Task is completed for All User Databases. ******'
SET ANSI_NULLS OFF
SET ARITHABORT OFF
SET QUOTED_IDENTIFIER OFF
END
**** It's running fine, but against few dbs, getting the below error ****
Database ADVENTUREWORKSLT2012
Msg 16958, Level 16, State 3, Procedure usp_updateStats_DBS_best, Line 58
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure usp_updateStats_DBS_best, Line 59
Could not complete cursor operation because the set options have changed since the cursor was declared.
Please suggest...if I am need anything else on my script to make it much better.
Thanks.
April 9, 2015 at 6:26 am
CREATE PROC usp_updateStatsAllDbs
AS
BEGIN
SET ANSI_NULLS ON
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
--Set quoted_identifier off
DECLARE@dataname VARCHAR(MAX)
DECLARE@dataname_header VARCHAR(MAX)
DECLARE datanames_cursor CURSOR
FOR
SELECTname
FROMsys.databases
WHEREdatabase_id IN ( SELECTdbid
FROMsys.sysdatabases
EXCEPT
SELECTdatabase_id
FROMmsdb..suspect_pages ) AND
name NOT IN ( 'master', 'msdb', 'tempdb', 'model', 'distribution' ) AND
state_desc = 'ONLINE';
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE ( @@fetch_status <> -1 )
BEGIN
IF ( @@fetch_status = -2 )
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
SELECT@dataname_header = 'Database ' + RTRIM(UPPER(@dataname))
PRINT ' '
PRINT @dataname_header
PRINT ' '
SELECT@dataname_header = RTRIM(UPPER(@dataname))
EXEC ('Use ' + @dataname)
EXEC sp_updatestats;
FETCH NEXT FROM datanames_cursor INTO @dataname
END
--CLOSE datanames_cursor
DEALLOCATE datanames_cursor
PRINT ' '
PRINT ' **** Update Stats Task is completed for All User Databases. ******'
SET ANSI_NULLS OFF
SET ARITHABORT OFF
SET QUOTED_IDENTIFIER OFF
END
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply