SQL Server: running some query on all databases

A little useful trick, lifted from somewhere:
sp_MSforeachdb 'use [?]
[additional statements come here]'
Any single quotes within the “additional statements” must be escaped with second single quote (not double quotes, as it may seem), e.g.:
sp_MSforeachdb 'use [?]
declare @tbl nvarchar(500)
select @tbl=name from sysobjects where type=''U'' and name=''MYTABLE''
if @res = ''MYTABLE''
begin	exec sp_spaceused
end'

This procedure will retrieve size of the database if it finds a specific table (called MYTABLE) in it.

Basically the most valuable point here is: to use the name of the current database, question mark is used, e.g.:

sp_MSforeachdb 'DBCC CHECKDB(''?'')'

Another similar procedure is sp_MSforeachtable.

Advertisements
SQL Server: running some query on all databases

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s