Some time back, I had done an article on 6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb
So now if you want to use this undocumented stored procedure to list the size of all databases in a SQL Server Instance, you would write the following query:
EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'
However if you wanted to exclude some databases from the query, then here’s how to do so:
EXEC sp_msforeachdb
"IF '?' NOT IN ('master','tempdb', 'model') BEGIN
USE ?; EXEC sp_spaceused
END"
This query will now list the size of all databases except the master, tempdb and model databases.
![image image](//lh4.ggpht.com/_0j4bzarlOBg/S0wj-18MUDI/AAAAAAAAAmw/Ictz2L3hXt4/image_thumb%5B1%5D.png?imgmax=800)
About The Author
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of
DotNetCurry,
DNC Magazine for Developers,
SQLServerCurry and
DevCurry. He has also authored a couple of books
51 Recipes using jQuery with ASP.NET Controls and a new one recently at
The Absolutely Awesome jQuery CookBook.
Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.
Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook
7 comments:
Hi I found your info very useful but I am having some trouble using it with databases using UUID as names.
example one database is named 00075926-0f40-41b1-8695-d7ca3fc7f474
now when I run a command like
EXEC sp_MSforeachdb 'USE ?; PRINT DB_NAME()'
I will get an error : Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '00075926'.
clearly the name of the database . Any Ideas on what I should do ?
Try this
EXEC sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'
Wow thanks madhivana! had the same problem, never knew you could use the USE statement like that
- B. Meyer
Thanks anonymous for the feedback
madhivana!
Super response you made my day :)
Thanks jbraes for the feedback
I want to exclude some staging databses from the list and run the query....
i mean
not like '%stg%
can we use it in this way so I can exclude the list of staging databases....
Post a Comment