May 6, 2008 at 5:41 am
Hi,
I am using below query to update the Statistics in one Db,
i need to update it for around 100 Db
Could you please check this and advise
DECLARE @tblName sysname, @sql nvarchar(100)
DECLARE @tbl TABLE
(name sysname)
INSERT @tbl
SELECT name
FROM sysobjects
WHERE xtype = 'U' and uid = 1
WHILE EXISTS(select top 1 * from @tbl)
BEGIN
SELECT TOP 1 @tblName = name from @tbl
SET @sql = 'UPDATE STATISTICS ' + @tblName + ' WITH SAMPLE 50 PERCENT'
EXEC sp_executesql @sql
-- PRINT @sql
DELETE @tbl WHERE name = @tblName
END
May 6, 2008 at 5:48 pm
This will work:
EDIT: No it won't! Bad code removed. See my next post.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 7, 2008 at 1:48 am
Thanks,very helpfull
May 7, 2008 at 3:35 pm
This is even more helpful - code that actually updates stats in all databases. My bad, I missed that the first sp_executesql statement changes the context, but only for that statement, so that code ends up updating stats in the same database over and over (d'oh).
This code works (I promise!):
[font="Courier New"]DECLARE @dbname sysname
DECLARE @sql nvarchar(4000)
DECLARE tmpcursor CURSOR FAST_FORWARD FOR
SELECT name FROM master..sysdatabases --for 2005 use master.sys.databases
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' -- for 2005, can use WHERE state_desc = 'ONLINE'
AND name <> 'tempdb'
OPEN tmpcursor
FETCH NEXT FROM tmpcursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE [' + @dbname + N']; '
+ N'SELECT DB_NAME(); '
+ N'DECLARE @tbl TABLE (name sysname); DECLARE @sql2 nvarchar(100), @tblName sysname; '
+ N'INSERT @tbl '
+ N' SELECT name '
+ N' FROM sysobjects '
+ N' WHERE xtype = ''U'' and uid = 1; '
+ N'WHILE EXISTS(SELECT TOP 1 * FROM @tbl) '
+ N'BEGIN '
+ N' SELECT TOP 1 @tblName = name FROM @tbl '
+ N' SET @sql2 = N''UPDATE STATISTICS ['' + @tblName + N''] WITH SAMPLE 50 PERCENT '' '
+ N' EXEC sp_executesql @sql2 '
+ N' DELETE @tbl WHERE name = @tblName '
+ N'END '
EXEC sp_executesql @sql
FETCH NEXT FROM tmpcursor INTO @dbname
END
CLOSE tmpcursor
DEALLOCATE tmpcursor[/font]
It may be able to be further refined, but it does the job. Not sure how wise it is to run nested sp_executesql statements, but nothing went horribly wrong on my test rig (SQL 2005 Dev).
Set your results window to text output - will give you the dbname followed by rowcounts.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply