When administering a larger database environment, sometimes one needs to perform repetitive tasks.  Performing repetitive tasks becomes more and more painful (maybe even demoralizing) with the larger the number of databases that might exist on a server.

This kind of administration will have you running in circles.  You may even feel like you are making progress and then suddenly feel like there is no end in sight as you repeat the process with each database.

Some of the tasks that you may need to perform on a regularly basis may be to run reports on security access across all databases for a particular user, or to retrieve file free space information for all databases, or you may even just need to update the statistics across multiple databases.

These tasks can be simplified and even automated to help free your sanity and free your time.  Here is a trio of examples.

Update Statistics

*Disclaimer* These examples are just that – examples.  They are meant to be simple introductions.  The development into a full solution for use in your environment is a project for you to undertake.

In all of the examples I will share, there will be a common theme.  I will employ the looping mechanism introduced via sp_MSforeachdb.  I will also introduce a better version of that proc and how to execute each of these scripts with the newer version.

Code block

EXECUTE sp_MSforeachdb 'use ?; Execute sp_updatestats;'
GO

As promised, that script is very simple to create.  I will leave it to you to make modifications for your environment.

User Exists

Suppose you get a request from your manager to list out the databases that a particular user has been granted access.  Here is a simple script to find which databases a user has been created within to report back to your manager.

Code block

CREATE TABLE #UserExists (DatabaseName VARCHAR(128), UserName VARCHAR(128));
GO
 
EXECUTE sp_MSforeachdb 'Insert Into #UserExists (DatabaseName, UserName)
select ''?'' AS DatabaseName, 
                name AS UserName 
                FROM [?].sys.database_principals
                WHERE name = ''guest'' ';
 
SELECT DatabaseName, UserName
FROM #UserExists;
 
DROP TABLE #UserExists;

Again, the script is not too terribly complex and can get the job done quickly.  The idea here is that each database will be queried on the database_principals catalog view.  I dump the results into a temp table for the each database in which that user exists and then query the temp table for the final result.

File Free Space

Occasionally you will want to know how much free space is in each database file.  This can be very useful should you manage your file growths.  You can use this information to establish alerts for when a file reaches a certain capacity threshold.

Code block

CREATE TABLE #FileProp (dbid INT,FILE_ID INT, SpaceUsed DECIMAL(14,2));
GO
 
EXECUTE sp_MSforeachdb 'USE ?; Insert Into #FileProp (dbid,FILE_ID,SpaceUsed) 
SELECT database_id,file_id,FILEPROPERTY(name,''SpaceUsed'') from sys.master_files where DB_NAME(database_id) = ''?'''
 
SELECT DB_NAME(database_id) AS DBName,physical_name,CONVERT(DECIMAL(14,2),SIZE)/128 AS FileSize, growth
,CONVERT(DECIMAL(14,2),max_size)/128 AS MaxFileSize,FP.SpaceUsed,mf.type_desc
FROM sys.master_files mf
INNER JOIN #FileProp FP
ON FP.dbid = mf.database_id
AND FP.FILE_ID = mf.FILE_ID;
 
DROP TABLE #FileProp;
GO

Similar to the previous query, this query dumps results for each database into a temp table.  From there, we then query that temp table to get the final result set.  Like the others, this is an example to help produce some ideas.  You can extend this type of query to meet your needs as you see fit.

Alternative

Gianluca Sartori did some good work on building a better procedure to replace sp_MSforeachdb (while not using a cursor).  You can find his work here.  The reason for the updated procedure is due to some limitations/bugs with sp_MSforeachdb.  If you decide to use the new version, here is an example of how you might run one of the previously discussed queries.

Code block

CREATE TABLE #UserExists (DatabaseName VARCHAR(128), UserName VARCHAR(128));
GO
 
EXECUTE dbo.[dba_ForEachDB] @STATEMENT = 'Insert Into #UserExists (DatabaseName, UserName)
select ''?'' AS DatabaseName, 
                name AS UserName 
                FROM [?].sys.database_principals
                WHERE name = ''guest'' '
,@replacechar = '?';
 
SELECT DatabaseName, UserName
FROM #UserExists;
 
DROP TABLE #UserExists;

Conclusion

Some simple examples of routine database administration tasks have been illustrated.  Take these examples and extend on them if you like.  It can save you some time.