Hvordan bruke SP_MSForEachdb i SQL Server

Når du jobber med databaser, vil du møte forekomster der du trenger å utføre et spesifikt sett med spørsmål på alle databasene. Det er en praktisk prosedyre kalt SP_MSForEachdb () i et slikt scenario. Denne prosedyren lar deg utføre et sett med kommandoer på hver database som er tilgjengelig i SQL Server -forekomsten.

Ved hjelp av denne guiden lærer du hvordan du bruker SP_MSForEachdb () lagret prosedyre, hvordan du bruker den, og forskjellige eksempler på hvordan du bruker prosedyren.

Sys.sp_msforeachdb ()

SP_MSForEachdb () er en udokumentert lagret prosedyre tilgjengelig i hoveddatabasen. Den lar deg sløyfe over alle databasene i SQL Server -forekomsten og utføre SQL -spørsmål mot de spesifiserte databasene.

I SQL Server Management Studio kan du se denne prosedyren ved å navigere til Master Database -> Programmerbarhet -> Lagrede prosedyrer -> System lagrede prosedyrer.

Vi kan uttrykke prosedyresyntaks som vist:

Erklære @command varchar (255)
Set @command = 'Kommandooperasjoner'
Exec sp_msforeachdb @command = command

La oss nå se på noen få eksempler på å bruke prosedyren.

EKSEMPEL 1-Viser navnene på alle databaser

Anta at du ønsker å få navnene på alle databasene i SQL Server -forekomsten; Du kan bruke MSForEachdb () -prosedyren som vist i eksemplet nedenfor:

Erklære @command varchar (255)
Set @command = 'bruk ? skriv ut db_name () '
Exec sp_msforeachdb @command

Ovennevnte sett med spørsmål skal returnere navnene på alle databasene i tilfellet. Et eksempelutgang er som vist:

herre
tempdb
modell
MSDB
Salesdb
Baseballdata
Wideworldimporters
Fullføringstid: 2021-12-14T02: 43: 45.8852391-08: 00

Eksempel 2 - Vis databasestørrelser

Selv om det er forskjellige måter du kan bruke for å få størrelsen på en database i SQL Server, vil vi i dette eksemplet bruke SP_Spaceused -prosedyren.

Tenk på eksemplet som er vist nedenfor:

Erklære @command varchar (255)
Set @command = 'bruk [?]; exec sp_spaceused '
Exec sp_msforeachdb @command

Ved hjelp av en enkelt kommando kan vi se størrelsen på alle databasene som vist i eksempelutgangen nedenfor:

Eksempel 3 - Vis alle kolonnene i databasene

For å se kolonnene i hver database, kan du utføre en spørring som vist i eksemplet utdraget nedenfor:

Erklære @command varchar (255);
Set @command = 'velg navn fra ?.sys.kolonner '
Exec sp_msforeachdb @command

Spørringen over skal returnere kolonnene i hver database som vist:

Eksempel 4 - krympe alle databaser

Du kan krympe størrelsen på alle databasene på serveren ved å bruke MSForEachdb -prosedyren som vist nedenfor:

Erklære @command varchar (255);
Set @command = 'dbcc shrinkdatabase (' '?'', 0) '
Exec sp_msforeachdb @command

Ovennevnte eksempel vil prøve å krympe størrelsen på alle databasene på serveren. Hvis du har en omfattende samling av databaser, unngå å bruke denne spørringen, da det kan ta lang tid og blokkere andre prosesser fra å bruke databasene.

Eksempelutgang er som vist:

Lukking

Lukking
Denne opplæringen viser deg hvordan du bruker SP_MSForEachdb () lagret prosedyre for å utføre SQL -spørsmål på alle databasene i SQL Server -forekomsten.

Takk for at du leser, og følg med for flere SQL Server -opplæringsprogrammer.