SQL Script to Update Usage for all databases

Leave a comment (1) Go to comments

Following script will be used to update stats for all databases at one go

declare @servername varchar(50), --variable to hold the servername
    @dbname varchar(100), --variable to hold the database name
    @command varchar(1000) --variable to hold the sql command

-- set variables
select @servername=@@servername

-- declare the cursor
declare dbccuu cursor for
select name from master.dbo.sysdatabases
    where  (status & 32 <> 32 and status & 128 <> 128 and status & 512 <> 512 and status & 1024 <> 1024 and status & 4096 <> 4096 and status & 2048 <> 2048)  and (name not in ('Northwind', 'Pubs'))

-- open the cursor
open dbccuu

-- fetch the first record into the cursor
fetch dbccuu into @dbname

-- while the fetch was successful
while @@fetch_status=0
begin
    -- print the header for each database
    print ''
    print '***************************'
    print 'DBCC UPDATEUSAGE Report For '+@DBNAME
    print '***************************'
    print ''

    -- set the command to execute
    set @command='dbcc updateusage('+@dbname+')'

    -- execute the command
    exec(@command)

    -- fetch the next record into the cursor
    fetch dbccuu into @dbname
end

-- close the cursor
close dbccuu

-- deallocate the cursor
deallocate dbccuu

-- tell user when the script was last run
select 'This script was executed on ' + cast(getdate() as varchar(50))
EOF - SQL Script to Update Usage for all databases, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

  1. I was going to write a simple Select against sys.databases.name concatenating with DBCC command and run the resulting script but the cursor you published does this with one less step.

    Thanks for sharing your script.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.