1. 유사패턴 이름을 가진 녀석들만 대상으로 할 수 있다.
2. 시스템 DB 혹은 사용자 DB만을 대상으로 할 수 있다.
3. 특정 호환성을 가진 녀석들만
4. 내가 원하는 것들만 골라서
등의 기능이 있습니다. 매우 유용합니다.
sp_foreachdb.pdfProblem
While the system procedure sp_MSforeachdb is neither documented nor officially supported, most SQL Server professionals have used it at one time or another. This is typically for ad hoc maintenance tasks, but many people (myself included) have used this type of looping activity in permanent routines. Sadly, I have discovered instances where, under heavy load and/or with a large number of databases, the procedure can actually skip multiple catalogs with no error or warning message. Since this situation is not easily reproducible, and since Microsoft typically has no interest in fixing unsupported objects, this may be happening in your environment right now.
Solution
In my environment, the minute I discovered the issue, I promptly wrote a replacement. (I blogged about my replacement earlier this year.) While I was writing the new stored procedure, it struck me that, while I was making my maintenance processes more reliable, I could also make them more flexible.
For example, I could have the procedure operate only on databases that:
- are system databases (master, msdb, model, tempdb);
- are non-system databases;
- match a specific name pattern;
- are in a comma-separated list of db names;
- have a specific recovery model or compatibility level;
- are read only or have auto-close or auto-shrink enabled; or,
- have service broker enabled.
Some other handy options I thought to add, which aren't in sp_MSforeachdb, include an option to print the database name before each result, or even to only print the command instead of executing. This can be very handy if you are trying to set a slew of databases to SINGLE_USER and don't want the operations to happen serially; you can print the commands and split the output across multiple Management Studio windows.
With all that said, here is the stored procedure in its current form:
USE [master];
GO
CREATE PROCEDURE dbo.sp_foreachdb
@command NVARCHAR(MAX),
@replace_character NCHAR(1) = N'?',
@print_dbname BIT = 0,
@print_command_only BIT = 0,
@suppress_quotename BIT = 0,
@system_only BIT = NULL,
@user_only BIT = NULL,
@name_pattern NVARCHAR(300) = N'%',
@database_list NVARCHAR(MAX) = NULL,
@recovery_model_desc NVARCHAR(120) = NULL,
@compatibility_level TINYINT = NULL,
@state_desc NVARCHAR(120) = N'ONLINE',
@is_read_only BIT = 0,
@is_auto_close_on BIT = NULL,
@is_auto_shrink_on BIT = NULL,
@is_broker_enabled BIT = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@sql NVARCHAR(MAX),
@dblist NVARCHAR(MAX),
@db NVARCHAR(300),
@i INT;
IF @database_list > N''
BEGIN
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY s1.name) - 1
FROM sys.objects AS s1
CROSS JOIN sys.objects AS s2
)
SELECT @dblist = REPLACE(REPLACE(REPLACE(x,'</x><x>',','),
'</x>',''),'<x>','')
FROM
(
SELECT DISTINCT x = 'N''' + LTRIM(RTRIM(SUBSTRING(
@database_list, n,
CHARINDEX(',', @database_list + ',', n) - n))) + ''''
FROM n WHERE n <= LEN(@database_list)
AND SUBSTRING(',' + @database_list, n, 1) = ','
FOR XML PATH('')
) AS y(x);
END
CREATE TABLE #x(db NVARCHAR(300));
SET @sql = N'SELECT name FROM sys.databases WHERE 1=1'
+ CASE WHEN @system_only = 1 THEN
' AND database_id IN (1,2,3,4)'
ELSE '' END
+ CASE WHEN @user_only = 1 THEN
' AND database_id NOT IN (1,2,3,4)'
ELSE '' END
+ CASE WHEN @name_pattern <> N'%' THEN
' AND name LIKE N''%' + REPLACE(@name_pattern, '''', '''''') + '%'''
ELSE '' END
+ CASE WHEN @dblist IS NOT NULL THEN
' AND name IN (' + @dblist + ')'
ELSE '' END
+ CASE WHEN @recovery_model_desc IS NOT NULL THEN
' AND recovery_model_desc = N''' + @recovery_model_desc + ''''
ELSE '' END
+ CASE WHEN @compatibility_level IS NOT NULL THEN
' AND compatibility_level = ' + RTRIM(@compatibility_level)
ELSE '' END
+ CASE WHEN @state_desc IS NOT NULL THEN
' AND state_desc = N''' + @state_desc + ''''
ELSE '' END
+ CASE WHEN @is_read_only IS NOT NULL THEN
' AND is_read_only = ' + RTRIM(@is_read_only)
ELSE '' END
+ CASE WHEN @is_auto_close_on IS NOT NULL THEN
' AND is_auto_close_on = ' + RTRIM(@is_auto_close_on)
ELSE '' END
+ CASE WHEN @is_auto_shrink_on IS NOT NULL THEN
' AND is_auto_shrink_on = ' + RTRIM(@is_auto_shrink_on)
ELSE '' END
+ CASE WHEN @is_broker_enabled IS NOT NULL THEN
' AND is_broker_enabled = ' + RTRIM(@is_broker_enabled)
ELSE '' END;
INSERT #x EXEC sp_executesql @sql;
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT CASE WHEN @suppress_quotename = 1 THEN
db
ELSE
QUOTENAME(db)
END
FROM #x ORDER BY db;
OPEN c;
FETCH NEXT FROM c INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = REPLACE(@command, @replace_character, @db);
IF @print_command_only = 1
BEGIN
PRINT '/* For ' + @db + ': */'
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ @sql
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
END
ELSE
BEGIN
IF @print_dbname = 1
BEGIN
PRINT '/* ' + @db + ' */';
END
EXEC sp_executesql @sql;
END
FETCH NEXT FROM c INTO @db;
END
CLOSE c;
DEALLOCATE c;
END
GOCaveats
The procedure doesn't cope well with databases with a single quote ( ' ) in their name or with leading / trailing spaces, but it gladly handles databases that violate other best practices, such as beginning with a number or containing special characters like ., ", [, or ]. Here is a quick list of databases that it has been tested against:
(Try creating those databases on your system and running EXEC sp_MSforeachdb 'SELECT * FROM ?.sys.objects;'; - you'll get a variety of errors.)
Also, you do not need to QUOTENAME parameter values... you should pass in 'master, model' to @database_list, not '[master], [model]', and you should use 'USE ?;' and not 'USE [?];' for the command and replace_character values - this escaping is handled for you. However, if you have a command where you want to be able to selectively choose whether or not to apply QUOTENAME to the replace_character (for example, @command = 'SELECT ''[?]'', * FROM sys.databases WHERE name = ''?'';'), you can use the override parameter @suppress_quotename.
While there are parsing solutions for all of these problems, they quickly explode the code and become more maintenance trouble than they're worth. At least, in this author's opinion.
Finally, the procedure does not currently include any logging or error handling, which you may want to add if you are going to use this type of procedure in any automated processes.
Examples
To perform a full backup to the same folder of all user databases that are in simple mode. This is one case where you'll want to use the @suppress_quotename parameter, otherwise you end up with files named [database_name].bak.
To search all databases matching the name pattern 'Company%' for objects matching the name pattern '%foo%'. Place into a #temp table so the result is a single result set instead of the number of databases that match the naming pattern.
To turn auto_shrink off for all databases where it is enabled:
To find the last created object date/time for each database in a defined set (in this case, three databases that I know exist).
To reset service broker for every database - after testing an application, for example:
Next Steps
- Create your own version of sp_foreachdb in master.
- Add logging and/or error handling according to the standards and conventions in your environment.
- Begin phasing the new procedure into processes where you are currently using sp_MSforeachdb.
- Review the following tips and other resources:
'Brain Trainning > DataBase' 카테고리의 다른 글
| [MSSQL] OLE DB 공급자 'Microsoft.Jet.OLEDB.4.0'은(는) 단일 스레드 아파트 모드에서 실행되도록 구성되어 있으므로 분산 쿼리에 사용할 수 없습니다. (0) | 2011.05.23 |
|---|---|
| [MSSQL] 압축된 테이블 찾기 (0) | 2011.05.11 |
| [MSSQL] MySQL Linked Server on SQL Server 2008 (0) | 2011.05.06 |
| [MSSQL] Excel 을 DB 처럼 (Openrowset) (0) | 2011.04.30 |
| [MSSQL] Creating Compressed Tables and Indexes (펌) (0) | 2011.04.28 |