Working
on multiple databases on a single instance, sometime you need to execute a
query for each database and for that sp_MSforeachdb is the best
choice.
Recently
talking to my development team I came to know that a very few guys have idea
about filter for sp_MSforeachDB.
For
example, if I need to get database physical files information for each database
on my instance, I will use following simple query
EXEC sp_MSforeachdb '
BEGIN
SELECT name,physical_name,state,size
FROM ?.sys.database_files
END'
BUT what if, I need to omit MSDB,
TempDB and Model databases for this query. Now I have to apply filter. This can
be achieved by simple IF statement.
EXEC sp_MSforeachdb 'IF ''?'' NOT IN
(''tempDB'',''model'',''msdb'')
BEGIN
SELECT name,physical_name,state,size
FROM ?.sys.database_files
END'
You can even
use ? sign in WHERE clause.
EXEC sp_MSforeachdb 'IF ''?'' NOT IN
(''tempDB'',''model'',''msdb'')
BEGIN
SELECT name,physical_name,state,size
FROM ?.sys.database_files
WHERE name
LIKE ''?%'' -- Only Files starting with DB name
END'
Output
can be saved in tables (user, temporary) or table variables
DECLARE @DatabasesSize TABLE
(
name VARCHAR(50),
physical_name VARCHAR(500),
state BIT,
size INT
)
INSERT INTO
@DatabasesSize
EXEC sp_MSforeachdb 'IF ''?'' NOT IN
(''tempDB'',''model'',''msdb'')
BEGIN
SELECT name,physical_name,state,size
FROM ?.sys.database_files
END'
Thank you. Very informative. However, sp_MSforeachDB is undocumented and known to miss DBs.
ReplyDeleteRegards,
Syed Muhammad Yasir
I love that command but for the life of me I can not figure out why it fails if you try to use it to set your RECOVERY model for all you user databases. Here's the script - any insight would solve one of my most puzzling mysteries...
ReplyDeleteEXEC sp_MSForEachDB 'if ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
use [?]
print ''?'' + '': SET RECOVERY SIMPLE WITH NO_WAIT''
ALTER DATABASE [?] SET RECOVERY SIMPLE WITH NO_WAIT
END
print ''?'' + '': SET RECOVERY SIMPLE WITH NO_WAIT - DONE'';
'
GO
RESULTS:
master: SET RECOVERY SIMPLE WITH NO_WAIT - DONE
Msg 5058, Level 16, State 1, Line 5
Option 'RECOVERY' cannot be set in database 'tempdb'.
model: SET RECOVERY SIMPLE WITH NO_WAIT - DONE
msdb: SET RECOVERY SIMPLE WITH NO_WAIT - DONE
Thank you very helpful
ReplyDeleteA good piece of information.
ReplyDeleteThank you.