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]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[UpdateStatsForAllDBS]
SELECT 'Return Value' = @return_value

GO 

Total article views: 1190 | Views in the last 30 days: 1
 
Related Articles
FORUM

User & Their Databases access with Access levels

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

FORUM

Statistics in all Databases

Statistics in all Databases

FORUM

User & Their Databases access with Access levels

Table with Users,Their DBs access,Permissions

ARTICLE

Database Permissions

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

BLOG

Limiting user access to your database

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

Tags
update stats    
 
Contribute