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.
Free SQL Comparison tools
Trusted by thousands of users
Download your copy now
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.
|