SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Update Statistics for databases where you have access

By Christopher Petrich,

At one time or another we work for an organization where access to the development server is limited to certain permissions on selected databases.  

While it is not unusual that statistics are stale on a development server, it can be troublesome debugging performance problems when statistics are old.    

I created this procedure to enable me to update statistics where I can.  The code will crawl through the list of databases where you have access and also have "Alter" permissions, running Update Statistics on each table of each database.  System database are skipped. Note that this uses the undocumented procedure "sp_MSforeachtable"

With data management folks understandably skittish about unauthorized access by even FTE staff, you had better get the manager's permission before running this, even if you have the technical rights.

USE [DBMaint]
DECLARE @return_value int
EXEC @return_value = [dbo].[UpdateStatsForAllDBS]
SELECT 'Return Value' = @return_value


Total article views: 1193 | Views in the last 30 days: 2
Related Articles

User & Their Databases access with Access levels

User,Databases Access,Read Write permissions...List


Statistics in all Databases

Statistics in all Databases


User & Their Databases access with Access levels

Table with Users,Their DBs access,Permissions


Database Permissions

Here is a short How To article on querying for permissions in a SQL Server database


Limiting user access to your database

There are situations where you need to grant SELECT permission to a particular user and limit that a...

update stats