SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



Create sp_refreshview for all Dependent Views in SQL Server

It is required to create sp_refreshview command and update view definition for all dependent views in a SQL Server database when underlying tables or database objects has been changed.

SQL Server database administrators and T-SQL developers can run the following sql code in order to list sql views dependent on other sql objects.

select
 s.name as schema_name,
 v.name as view_name,
 v.object_id,
 d.referenced_entity_name,
 d.referenced_id
from sys.views v
inner join sys.schemas s on v.schema_id = s.schema_id
left join sys.sql_expression_dependencies d
 on v.object_id = d.referencing_id
where d.is_schema_bound_reference = 0

Please note that the field is_schema_bound_reference is identifying that the relation between the SQL Server view and the database object is schema bounded or not. Because if sql view is schema bound created, before the underlying object is altered the SQL view should be changed. Otherwise schema bound property will not allow undelying dependent object change.



After the dependent SQL views in a SQL Server database is listed, we can call each view name within a SQL cursor and execute the system procedure sp_refreshview in order to update the definition of the view object.

Declare @view sysname

DECLARE view_cursor CURSOR FAST_FORWARD FOR
select v.name
from sys.views v
inner join sys.schemas s on v.schema_id = s.schema_id
inner join sys.sql_expression_dependencies d
 on v.object_id = d.referencing_id
where d.is_schema_bound_reference = 0


OPEN view_cursor

FETCH NEXT FROM view_cursor INTO @view

WHILE @@FETCH_STATUS = 0
BEGIN
-- print @view
 execute sp_refreshview @view

 FETCH NEXT FROM view_cursor INTO @view
END

CLOSE view_cursor
DEALLOCATE view_cursor

The above sql script will refresh SQL Server view object definitions one by one in each step of the T-SQL cursor

SQL developers can refer to t-sql tutorial SQL Server sp_refreshview to Update View Definition for sp_refreshview procedure and to Sample SQL Cursor for SQL Server cursor syntax and usage.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems