Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_MSforeachview - Perform an action on each view Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 10:56 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, June 4, 2014 12:29 PM
Points: 1,931, Visits: 234
Comments posted to this topic are about the item sp_MSforeachview - Perform an action on each view

Brian Knight
Free SQL Server Training Webinars
Post #401613
Posted Thursday, July 16, 2009 10:09 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:42 PM
Points: 635, Visits: 2,215
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.
Post #754332
Posted Friday, May 25, 2012 12:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:12 PM
Points: 10, Visits: 118
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. Took me a minute (and a helpful blog from a fellow in Romania) to get that.

**added this to another article 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

Post #1306775
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse