query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

monty
Posting Yak Master

130 Posts

Posted - 2008-08-12 : 14:11:22

hi gurus,

i am trying to put together this script for updating stastics but some thing i am missing out can any one plese help out

SET NOCOUNT ON

DECLARE @dbid int, @DBName sysname, @SQL nvarchar(4000), @version char(1)

SELECT @dbid = 5, @version = CONVERT(char(1), SERVERPROPERTY('ProductVersion'))

IF @version <> '9'
BEGIN
WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases WHERE dbid > @dbid AND status & 32 = 0)
BEGIN
SELECT TOP 1 @dbid = dbid, @DBName = name
FROM master.dbo.sysdatabases
WHERE dbid > @dbid AND status & 32 = 0
ORDER BY dbid

SET @SQL = ([' 'use'+ @DBName + '])''sp_updatestats''

EXEC sp_executesql @statement = @SQL
END
END

its me monty

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 14:18:58

http://weblogs.sqlteam.com/tarad/archive/2006/08/14/11194.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

monty
Posting Yak Master

130 Posts

Posted - 2008-08-12 : 14:25:27

thanks for the reply tara,

what i am actaully looking for is that sp should take of the parametres something like your checkdb sp.. i dont want to pass any i/p parametres all db's and tables should be taken care

its me monty

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 14:30:35

No need to add that to your code, just loop through them outside of the script/sproc:
http://weblogs.sqlteam.com/tarad/archive/2007/11/28/60417.aspx

Also check this out:
http://weblogs.sqlteam.com/tarad/archive/2008/06/30/SQL-Server-jobs-on-production-instances.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

monty
Posting Yak Master

130 Posts

Posted - 2008-08-12 : 14:38:29

must say Tara you rock

much thanks

its me monty

monty
Posting Yak Master

130 Posts

Posted - 2008-08-12 : 15:01:18

got a doubt Tara doesn't it show me the progress of updating in each table as it does in your checkdb script

its me monty

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 15:02:56

My checkdb script doesn't show any progress. You could write to a table to include this information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

monty
Posting Yak Master

130 Posts

Posted - 2008-08-12 : 15:27:49

the updatestats script just shows that it got executed sucessfully but when i use STATS_DATE to find out las modified stats it tells me null, i believe i am doing some thing wrong.. the checkdb script does tell me that so it hs no allocation and consiatenscy erros for each all objects..

its me monty

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 15:31:50

That's just the output of DBCC CHECKDB, and not directly from my script.

I don't have an answer as to your stats issue. Add some PRINT statements to check for progress.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources