Propagate view changes.

Question

Propagate view changes.

peacesells

SSCrazy

Points: 2882
More actions
March 25, 2013 at 6:20 pm

#272488

For security reasons, we have exact same view ( with same name) in different schemas based on different clients. Now if we have 100 clients, a change in one view or a if an new view is added to that that schema, they need be propagated to all other views in other schemas. I was wondering about a convenient yet easy approach to reflect these changes in a one view and propagate them to all the others schemas. Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

You could do something with Dynamic SQL and a DDL Trigger, but that would be code-compiling-code-that-it-wrote-on-the-fly which can be difficult to write to where it works properly in all cases, and even more difficult to debug when it doesn't.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

peacesells SSCrazy Points: 2882 More actions · Answer 2

opc.three (3/25/2013)
You could do something with Dynamic SQL and a DDL Trigger, but that would be code-compiling-code-that-it-wrote-on-the-fly which can be difficult to write to where it works properly in all cases, and even more difficult to debug when it doesn't.

I was thinking of using View Definition from INFORMATION_SCHEMA.VIEWS but the problem is it only stores upto 4000 chars. My other option was to use the sp_helptext system procedure and use it with dynamic query to regenerate the exact view in different schemas but i have some difficulty.

What i am trying to do is capture the result set of sp_helptext and replace it with bunch of replacements and make it build the view for different client with same structure. But for some reason it seem to loop through each line of the out of sp_helptext.

Below is the a snippet of the query..any help would be appreicated.

SET NOCOUNT ON;

DECLARE @ViewList NVARCHAR (500)='Accounting.Report'

DECLARE @ViewName NVARCHAR (500)

DECLARE @DomainName NVARCHAR(50)='Finance'

DECLARE @ClientName NVARCHAR(50)='Nike'

DECLARE @IfExistsSql NVARCHAR(MAX)=''

DECLARE @GetView TABLE(ViewList NVARCHAR(MAX))

DECLARE @r NVARCHAR (MAX)

INSERT INTO @GetView EXEC sp_helptext @ViewList

SET @IfExistsSql= 'IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'''+'['+@ClientName+']'+'.'+'['+@ViewName+']'+'''))

DROP VIEW' +' ['+@ClientName+']'+'.'+'['+@ViewName+']' +CHAR(10)+'GO'+CHAR(10)

SET @r=''

SELECT @r=@R+REPLACE(REPLACE(REPLACE(ViewList,'Finance',@ClientName),'<>','='),'RNT/1003',@DomainName) FROM @GetView

SELECT @r=@R+@IfExistsSql

PRINT ( @r )

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

INFORMATION_SCHEMA is there to serve certain clients that want to maintain ANSI-standard code and sp_helptext is not going to be very easy to work with. Instead, try using this query that uses the SQL Server-specific Catalog Views to retrieve the definitions:

SELECT s.name,

v.name,

m.definition

FROM sys.views v

JOIN sys.schemas s ON v.schema_id = s.schema_id

JOIN sys.sql_modules m ON v.object_id = m.object_id;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Related thread (for anyone answering)

http://www.sqlservercentral.com/Forums/Topic1437147-391-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass