Coding for removing servername from the views.

Coding for removing servername from the views.

 

-- The following script is to find out the existing views of the database machine's display

 

Use Database;

Select

B. Name,

C. referenced_server_name,

C. referenced_database_name,

C. referenced_schema_name,

A. definition,

B. create_date,

B. modify_date

From SYS. SQL _modules as

Join

SYS. Objects as B

On a. object_id = B. object_id

Join

SYS. SQL _expression_dependencies as C

On B. object_id = C. referencing_id

Where B. type = 'V'

And C. referenced_server_name is not null

 

-- The following script is to modify the database view exists after the replacement of the Processing Machine

 

 

Set nocount on;

Declare @ definition varchar (max)

Declare @ servern varchar (50)

Declare view_cursor cursor scroll

For (

Select

C. referenced_server_name,

A. Definition

From SYS. SQL _modules as

Join

SYS. Objects as B

On a. object_id = B. object_id

Join

SYS. SQL _expression_dependencies as C

On B. object_id = C. referencing_id

Where B. type = 'V'

And C. referenced_server_name is not null

)

Open view_cursor;

Fetch next from view_cursor into @ servern, @ definition;

While @ fetch_status = 0

Begin

Select @ definition = Replace (@ definition, 'create view', 'alter view ')

Select @ definition = Replace (@ definition, '[' [email protected] + ']' + '.','')

-- Print (@ definition );

Exec (@ definition );

Fetch next from view_cursor into @ servern, @ definition;

End

Close view_cursor;

Deallocate view_cursor;

Go

 

 

 

Coding for removing servername from the views.