sp_MSforeachview - Perform an action on each view

• Brian Knight

  One Orange Chip

  Points: 27259

  More actions

  September 23, 2007 at 10:56 am

  #62463

  Comments posted to this topic are about the item sp_MSforeachview - Perform an action on each view

  Brian Knight
  Free SQL Server Training Webinars

• Jim P.

  SSCrazy Eights

  Points: 8725

  More actions

  July 16, 2009 at 10:09 am

  #1025766

  I'm trying to use this in SQL 2005, and am running into an issue with the sp_MSforeach_worker. It is only setup to handle tables or databases.

  if @worker_type=1

  set @local_cursor = hCForEachDatabase

  else

  set @local_cursor = hCForEachTable

  How would the sp_MSforeach_worker (or an additional/replacement one) need to be coded to handle the views, etc?

  
  
  ----------------
  Jim P.

  A little bit of this and a little byte of that can cause bloatware.

• rockfordphile

  Old Hand

  Points: 370

  More actions

  May 25, 2012 at 12:33 pm

  #1493340

  Just had a go at this myself, ran into some errors, thought I'd post what needed to be done for SQL 2008 R2 to hopefully help the next guy get through it a bit faster.

  The key, as mentioned above, is the changed procedure. After that, it's important to change SET QUOTED IDENTIFIER OFF - not ON, as in the original article code[/url]. Took me a minute (and a helpful blog from a fellow in Romania) to get that.

  **added this to another article[/url] of Brian's which references the same topic, JIC

  USE MASTER

  GO

  if exists (select name from sysobjects

  where name = 'sp_MSforeachview' AND type = 'P')

  drop procedure sp_MSforeachview

  GO

  SET QUOTED_IDENTIFIER OFF

  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 hCForEachTable cursor global for select ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id =

  syso.object_id '

  + 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 = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

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

  exec(@postcommand)

  return @retval

  GO

  SET ANSI_NULLS ON

  GO