A Trio of EachDB

Comments: 1 Comment
Published on: July 17, 2012

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.

[codesyntax lang="tsql"]

[/codesyntax]

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.

[codesyntax lang="tsql"]

[/codesyntax]

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.

[codesyntax lang="tsql"]

[/codesyntax]

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.

[codesyntax lang="tsql"]

[/codesyntax]

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.

1 Comment - Leave a comment
  1. Nicely done, Jason!
    Thanks for sharing (and for the mention).

Leave a comment

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 class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
July 2012
M T W T F S S
« Jun   Sep »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, October 21, 2014