May 10, 2012

sp_MSdropdynsnapshotvws (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSdropdynsnapshotvws(nvarchar @dynamic_snapshot_views_table)

MetaData:

 create procedure sys.sp_MSdropdynsnapshotvws (  
@dynamic_snapshot_views_table sysname
)
as
begin
set nocount on
declare @retcode int
declare @final_retcode int
declare @select_command nvarchar(4000)
declare @old_dynamic_snapshot_view_name sysname
declare @dynamic_snapshot_view_name sysname
declare @drop_view_command nvarchar(4000)
declare @select_param_list nvarchar(4000)

select @final_retcode = 0
select @retcode = 0
-- Security check
exec @retcode = sys.sp_MSreplcheck_publish
if @@error<>0 or @retcode<>0
begin
return 1
end

-- For the first fecth, don't do comparison with the old view name...
select @dynamic_snapshot_view_name = null
select @select_command = N'
select @dynamic_snapshot_view_name = min(dynamic_snapshot_view_name)
from '
+ quotename(@dynamic_snapshot_views_table)

select @select_param_list =
N'@dynamic_snapshot_view_name sysname output'

exec @retcode = sys.sp_executesql @select_command,
@select_param_list,
@dynamic_snapshot_view_name = @dynamic_snapshot_view_name output
if @@error<>0 or @retcode<>0
begin
select @final_retcode = 1
end

-- Change query to do comparison with the old name
select @select_command = N'
select @dynamic_snapshot_view_name = min(dynamic_snapshot_view_name)
from '
+ quotename(@dynamic_snapshot_views_table) + N'
where dynamic_snapshot_view_name > @old_dynamic_snapshot_view_name'


select @select_param_list =
N'@dynamic_snapshot_view_name sysname output,
@old_dynamic_snapshot_view_name sysname'


-- Keep selecting view with the smallest name that
-- is greater than the last one until we are done
while @dynamic_snapshot_view_name is not null
begin

select @drop_view_command = N'drop view ' +
quotename(@dynamic_snapshot_view_name)
exec(@drop_view_command)
if @@error<>0
begin
select @final_retcode = 1
end

-- Drop the temporary function created for the dynamic snapshot view
select @drop_view_command = 'drop function dbo.' +
quotename(@dynamic_snapshot_view_name + '_FN')
exec(@drop_view_command)
if @@error<>0
begin
select @retcode = 1
end

delete MSdynamicsnapshotviews
where dynamic_snapshot_view_name = @dynamic_snapshot_view_name
if @@error<>0
begin
select @final_retcode = 1
end

select @old_dynamic_snapshot_view_name = @dynamic_snapshot_view_name
select @dynamic_snapshot_view_name = null
exec @retcode = sys.sp_executesql @select_command,
@select_param_list,
@dynamic_snapshot_view_name = @dynamic_snapshot_view_name output,
@old_dynamic_snapshot_view_name = @old_dynamic_snapshot_view_name

if @@error<>0 or @retcode<>0
begin
select @final_retcode = 1
end
end
-- At the end, we need to drop the temp table
declare @drop_table_command nvarchar(4000)
select @drop_table_command = N'drop table ' + quotename(@dynamic_snapshot_views_table)
exec(@drop_table_command)
if @@error<>0
begin
select @final_retcode = 1
end
return @final_retcode
end

No comments:

Post a Comment

Total Pageviews