אופן השימוש בפרוצדורות הלא-מתועדות sp_MsForEachDB ו-sp_MsForEachTable - ilDBA Portal

אופן השימוש בפרוצדורות הלא-מתועדות sp_MsForEachDB ו-sp_MsForEachTable

11/08/2011 | פורסם על ידי

הקדמה:

עבודת ה-DBA הינה לעיתים סיזיפית. אנו נדרשים לבצע את אותן פעולות על כל בסיסי הנתונים בכל השרתים – לפעמים זה גיבוי, לפעמים בדיקה, לפעמים כיווץ ועוד.
לעיתים גם אנו נדרשים לבצע פעילויות סוחפות בתוך בסיס הנתונים עצמו על כלל טבלאות המשתמש כגון ראורגניזציה, אישוש אינדקסים ועוד.

אפשר כמובן להכין תוכניות תחזוקה מובנות לכך או jobs  שמריצים פרוצדורות שכתבנו מבעוד מועד אבל כמובן שלא כל משימה תיכלל שם ויכולות להיות משימות רבות ומגוונות.
לצורך עזרה בנושא זה קיימות ב-master DB שתי פרוצדורות בשם sp_MsForEachDB ו-sp_MsForEachTable.

אלו הן צמד פרוצדורות לא מתועדות אך מאוד שימושיות מכיוון שהן עובדות על כל בסיסי הנתונים בשרת (SP_MSFOREACHDB) או על כל טבלאות המשתמש (SP_MSFOREACHTABLE) ובכך חוסכות כתיבת לולאות עם cursors מיותרים, בניית DYNAMIC SQL להרצת הקוד בלולאות וכו'.

סינטקס הפקודות הכללי הינו כך: (כנ"ל ל- sp_MsForEachDB):

Exec sp_MSforeachtable @command1,
                       @replacechar,
                       @command2,
                       @command3,
                       @whereand,
                       @precommand,
                       @postcommand
  • @command1 – פקודה ראשונה לביצוע
  • @replacechar – תו בפקודה שיוחלף בשם הטבלה בזמן הריצה ברירת המחדל היא "?"
  • @command2 and @command3 –  פקודה שנייה ושלישית אופציונאלי
  • @whereand – פילטר לסינון אובייקטים שאיננו רוצים שיכללו בריצה
  • @precommand – פקודה אופציונאלית שתרוץ פעם אחת לפני הרצת הלולאה על כל הטבלאות/בסיסי הנתונים
  • @postcommand – פקודה אופציונאלית שתרוץ פעם אחת אחרי הרצת הלולאה על כל הטבלאות/בסיסי הנתונים

דוגמאות לשימוש:

1) קבלת דוח מבנה כל טבלאות המערכת (שימוש ב-sp_MsForEachTable)

Use Northwind
GO

declare @cmd1 varchar(500)
set @cmd1 = 'exec sp_Help "?"'
exec sp_MSforeachtable @command1=@cmd1
GO

2) קבלת דוח מבנה כל האינדקסים (שימוש ב-sp_MsForEachTable)

Use Northwind
Go

declare @cmd1 varchar(500)
set @cmd1 = 'exec sp_HelpIndex "?"'
exec sp_MSforeachtable @command1=@cmd1
GO

3) ביצוע אישוש אינדקסים לכל טבלאות בסיס הנתונים  כולל מתן FILL FACTOR =80 (שימוש ב -sp_MsForEachTable)

USE Northwind
GO

EXEC sp_MSforeachtable
@command1="print '*'; DBCC DBREINDEX ('*', ' ', 80)" ,@replacechar='*'
GO

4) גיבוי כל בסיסי הנתונים בשרת ובדיקת תקינות כל בסיסי הנתונים בשרת (שימוש ב-sp_MsForEachDB)

Use master
GO

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
set @cmd1 = 'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''
set @cmd2 = 'if ''?'' <> ''tempdb'' backup database ? to disk=''c:\temp\?.bak'''
set @cmd3 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'

exec sp_MSforeachdb @command1=@cmd1,
                    @command2=@cmd2,
                    @command3=@cmd3
GO

5) הדפסת דוח מאפייני קבצים : גודל , צורת גידול,שימוש לכל בסיסי הנתונים בשרת (שימוש ב-sp_MsForEachDB):

Use master
GO

EXEC sp_MSforeachdb @command1='USE *;EXEC sp_helpfile ', @replacechar='*'
GO

6) הדפסת דוח של כל הטבלאות שאינן נמצאות בבסיסי נתונים של SYSTEM (שימוש ב-sp_MsForEachDB)

USE master
go

EXEC sp_MSforeachdb
@command1='USE ? SELECT OBJECT_NAME(object_Id) FROM sys.tables where DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')',@replacechar='?'
GO

7) הדפסת דוח נפחים של כל בסיסי הנתונים בשרת (שימוש ב-sp_MsForEachDB)

USE master
GO

EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'

לסיכום:

–   במאמר זה הראנו לכם דוגמאות מגוונות לשימוש בפרוצדורות   sp_MsForEachDB, sp_MsForEachTable

–   פרוצדורות אלו יכולות לבצע עבורנו כל פקודה שעובדת על כל בסיסי הנתונים/טבלאות בתוך בסיס נתונים ללא צורך בקידוד לולאות , כתיבת DYNAMIC SQL וכו' ובכך לחסוך מאיתנו כתיבת קוד T-SQL מעצבן ומיותר.

נתראה ברשומה הבאה!

אלי לייבה, DBA אפליקטיבי בכיר ומפתח אפליקציות בחברת החשמל,
בעל 19 שנות ותק בתחום מסדי נתונים ותכנות.
ניתן לפנות לאלי במייל: iecdba@hotmail.com

4 תגובות ל- “אופן השימוש בפרוצדורות הלא-מתועדות sp_MsForEachDB ו-sp_MsForEachTable”

גרי רשף | 21/08/2011 בשעה 04:32
commenter

תודה רבה אלי- את הפקודה אני אמנם מכיר מזמן, אך בפעם הראשונה אני רואה תיעוד מסודר שלה והסבר כה טוב.

אלי לייבה | 21/08/2011 בשעה 09:43
commenter

תודה גרי טוב לשמוע ממך
בעברית באמת לא היה תעוד מסודר של פקודות אלו
ומצאתי לנכון לתעד
הפקודות מאוד שמושיות למרות שאינן מתועדות ע"י מיקרוסופט וחבל

אלכס פרידמן | 06/09/2011 בשעה 10:24
commenter

נתקלתי במקרים בהם sp_MSforeachdb דילג על בסיסי נתונים באופן אקראי ללא כל הודעת שגיאה.
קיים כלי חלופי שנכתב ע"י Aaron Bertrand, מומלץ מאד:
http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
 

commenter

מה עם העובדה שהקוד שניתן לרשום בפקודה
MSFOREACHDB
מוגבל ל-144 תווים (משהו באזור)
קרה לי הרבה פעמים שלא יכולתי להשתמש בפקודה כיוון שהקוד שלי היה ארוך מדי.

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

הפונקציה COALESCE

כולם מכירים את הפונקציה NVL, ומשתמשים בה לא מעט. לעומת זאת, הפונקציה COALESCE, השייכת לאותה משפחה של פונקציות, פחות מוכרת, למרות שהיא קיימת כבר מגרסה 9i ועולה ביכולותיה על NVL הישנה והטובה. מצד שני, אף [...]

טיפ: איך לזכור ב-SQLPLUS שאתה בסביבת היצור

כמה פעמים קרה לנו שעבדנו עם sqlplus  מול סביבת יצור ביחד עם עוד כמה חלונות פתוחים ועם קצת לחץ? בקיצור כרגיל… מכוון שהכול כל כך לחוץ, הרצנו איזה פקודה הרסנית, ומיד הצטערנו עליה מאוד: אוי! הרצתי [...]
עבודה

עבודה תחת סביבת FireWall

אורי לרנר בטיפ קצר ושימושי על עבודה בסביבת אורקל [...]
Indexes

Indexes in SQL Server 2

ערן שקד ממשיך בסדרת מאמרים על אינדקסים בSQL [...]
Copyright 2020 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: