Technical Article

sp_MSforeachview - Perform an action on each view

,

This stored procedure loops through each view and performs an action on that view, like counting or printing the records. Please install this stored procedure in the MASTER database. For full usage instructions and advanced parameters please see the article about it at

http://www.sqlservercentral.com/columnists/bknight/sp_msforeachworker.asp.



Shortened version of the usage (there are a lot more parameters that can be seen on the article.):
   sp_MSforeachview @COMMAND1 = "Print '?'"

USE MASTER
GO

if exists (select name from sysobjects 
where name = 'sp_MSforeachview' AND type = 'P')
drop procedure sp_MSforeachview
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create proc sp_MSforeachview
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null,
   @command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
/* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set *//* @precommand and @postcommand may be used to force a single result set via a temp table. */
/* Preprocessor won't replace within quotes so have to use str(). */declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))

if (@precommand is not null)
exec(@precommand)

/* Create the select */   exec(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o '
         + N' where OBJECTPROPERTY(o.id, N''IsView'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
         + @whereand)
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

if (@retval = 0 and @postcommand is not null)
exec(@postcommand)

return @retval

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating