December 10, 2007 at 7:51 am
Need a second pair of eyes on this. I run this script on each database on our SQL server to update statistics. The job fails on databases that have tables that are not owned by the dbo role. This is SQL 2005 with compatibility at 80.
It appears the script I am using is geared towards tables that only have dbo as the owner...I guess I am missing where I can tweak the script to include all TABLE_SCHEMA users
Just looking for avenues:
SET NOCOUNT ON
GO
DECLARE updatestats CURSOR FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
OPEN updatestats
DECLARE @tablename NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
FETCH NEXT FROM updatestats INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'UPDATING STATISTICS ' + @tablename
SET @Statement = 'UPDATE STATISTICS ' + @tablename + ' WITH FULLSCAN'
EXEC sp_executesql @Statement
FETCH NEXT FROM updatestats INTO @tablename
END
CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT OFF
GO
December 10, 2007 at 8:39 am
You need to change script to include owner of object. It should look something like this:
DECLARE updatestats CURSOR FOR
SELECT table_schema,table_name FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
OPEN updatestats
DECLARE @tableschema NVARCHAR(128)
DECLARE @tablename NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
FETCH NEXT FROM updatestats INTO @tableschema,@tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'UPDATING STATISTICS ' + @tablename
SET @Statement = 'UPDATE STATISTICS [' + @tableschema + '].[' + @tablename + '] WITH FULLSCAN'
print @Statement
EXEC sp_executesql @Statement
FETCH NEXT FROM updatestats INTO @tableschema,@tablename
END
CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT OFF
GO
rgds
Sinisa
December 10, 2007 at 8:47 am
thanks for the help that works...(and simple) you can tell it is Monday for me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply