May 10, 2012

sp_MSdefer_check (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_MSdefer_check(nvarchar @objname
, nvarchar @objowner)

MetaData:

 create procedure sys.sp_MSdefer_check @objname sysname, @objowner sysname = NULL  
as
set nocount on
declare @cnstname sysname
declare @cnstid int
declare @objid int
declare @enable_cmd nvarchar(4000)
declare @disable_cmd nvarchar(4000)
declare @quotedproc nvarchar(240)
declare @dest nvarchar(514)
declare @dbname sysname
declare @proc_exists bit

if(@objowner is not null)
select @dest = quotename(@objowner) + N'.' + quotename(@objname)
else
select @dest = quotename(@objname)

declare @retcode int
IF @objname IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@objname', 'sp_MSdefer_check')
RETURN (1)
END

EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)

select @objid = object_id(@dest)
select @enable_cmd = N'sp_MSenable_' + convert(varchar(64), @objid)
if exists(select name from sys.objects where name = @enable_cmd and ObjectProperty(object_id, 'IsProcedure') = 1)
select @proc_exists = 1
else
select @proc_exists = 0

select @quotedproc = quotename(@enable_cmd)
select @enable_cmd = N'create procedure ' + @quotedproc + N' as '
create table #proccmd (c1 int identity, c2 nvarchar(3000) collate database_default)
insert #proccmd (c2) values (@enable_cmd)

declare ms_crs_cnst cursor local static for
select name, object_id
from sys.objects
where parent_object_id = @objid
and OBJECTPROPERTY(object_id, 'CnstIsDisabled') = 0
and OBJECTPROPERTY(object_id, 'CnstIsNotRepl') = 0
and (OBJECTPROPERTY(object_id, 'IsCheckCnst') = 1
or OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
union
select name, object_id
from sys.triggers
where parent_id = @objid
and OBJECTPROPERTY(object_id, 'IsTrigger') = 1
and OBJECTPROPERTY(object_id, 'ExecIsTriggerNotForRepl') = 0
and OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled') = 0
for read only

open ms_crs_cnst
fetch ms_crs_cnst into @cnstname, @cnstid
while @@fetch_status >= 0
begin
if(ObjectProperty(@cnstid, 'IsTrigger') = 1)
begin
select @disable_cmd = N'alter table ' + @dest + N' disable trigger ' + quotename(@cnstname)
select @enable_cmd = N'alter table ' + @dest + N' enable trigger '+quotename(@cnstname)
end
else
begin
select @disable_cmd = N'alter table ' + @dest + N' nocheck constraint ' + quotename(@cnstname)
select @enable_cmd = N'alter table ' + @dest + N' check constraint ' + quotename(@cnstname)
end
insert #proccmd (c2) values (@enable_cmd)

execute(@disable_cmd)
fetch ms_crs_cnst into @cnstname, @cnstid
end -- of major loop
deallocate ms_crs_cnst
if(@proc_exists = 1) -- don't try to recreate the proc
begin
select N'exec ' + @quotedproc
drop table #proccmd
return 0
end

select @enable_cmd = N'drop proc ' + @quotedproc
insert #proccmd (c2) values (@enable_cmd)
select @enable_cmd = N'select c2 from #proccmd order by c1'
select @dbname = db_name()
exec @retcode = sys.xp_execresultset @enable_cmd, @dbname
if @@error <> 0 or @retcode <> 0 or @quotedproc is NULL
begin
declare @cmd_param nvarchar(4000)
if (@objowner is null)
select @cmd_param = N' @objname = N''' + @objname + N''''
else
select @cmd_param = N' @objname = N''' + @objname + N''', @objowner = N''' + @objowner + N''''
select N'exec sp_MSreenable_check ' + @cmd_param
end
else
select N'exec ' + @quotedproc
drop table #proccmd
return 0

No comments:

Post a Comment

Total Pageviews