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 sys.sp_procoption(
varchar @OptionName,
varchar @OptionValue,
nvarchar @ProcName)
create procedure sys.sp_procoption @ProcName nvarchar(776) ,@OptionName varchar(35) ,@OptionValue varchar(12) as -- DECLARE VARIABLES declare @tabid int ,@uid int ,@opt_value int ,@opt_flag bit ,@dbname sysname -- DISALLOW USER TRANSACTION -- set nocount on set implicit_transactions off if @@trancount > 0 begin raiserror(15002,-1,-1,'sys.sp_procoption') return @@error end -- VALIDATE OPTION NAME AND VALUE select @opt_flag = case when (lower(@OptionValue) in ('1' ,'on' ,'yes' ,'true')) then 1 when (lower(@OptionValue) in ('0' ,'off' ,'no' ,'false')) then 0 end if (@opt_flag is null) OR (isnull(lower(@OptionName), '') <> 'startup') begin raiserror(15600,-1,-1, 'sys.sp_procoption') return @@error end -- RESOLVE GIVEN OBJECT NAME -- select @tabid = object_id, @uid = schema_id from sys.objects where object_id = object_id(@ProcName, 'local') and type in ('X','P','PC') if @tabid is null begin raiserror(15165,-1,-1,@ProcName) return @@error end BEGIN TRANSACTION -- LOCK PROC & CHECK PERMISSION -- EXEC %%Object(MultiName = @ProcName).LockMatchID(ID = @tabid, Exclusive = 1, BindInternal = 0) if @@error <> 0 begin ROLLBACK TRANSACTION raiserror(15165,-1,-1,@ProcName) return @@error end -- MUST BE sysadmin (Startup-procs run as sysadmin) -- if is_srvrolemember('sysadmin') = 0 begin -- In case of failure Audit the event. Note in case of success the event will be audited when we lock the -- the object in exclusive mode and evaluate the permissions again through the security manager. if not (@tabid is null) begin EXEC %%System().AuditEvent(ID = 1128350287, Success = 0, TargetLoginName = NULL, TargetUserName = NULL, Role = NULL, Object = @ProcName, Provider = NULL, Server = NULL) end ROLLBACK TRANSACTION raiserror(15165,-1,-1,@ProcName) return @@error end -- STARTUP PROC MUST BE OWNED BY DBO IN MASTER -- if (db_id() <> 1 OR @uid <> 1) begin ROLLBACK TRANSACTION raiserror(15398,-1,-1) return @@error end -- VALID OBJECT IN DATABASE? -- if @tabid is null begin ROLLBACK TRANSACTION select @dbname = db_name() raiserror(15009,-1,-1 ,@ProcName, @dbname) return @@error end -- PROC CANNOT HAVE PARAMETERS -- if exists ( select * from sys.parameters where object_id = @tabid ) begin ROLLBACK TRANSACTION raiserror(15399,-1, -1) return @@error end EXEC %%Module(ID = @tabid).SetStartup(Value = @opt_flag) -- Set Config option for startup procs set @opt_value = case when exists (select * from sys.objects where type in ('X','P','PC') and ObjectProperty(object_id, 'ExecIsStartup') = 1) then 1 else 0 end EXEC %%ServerConfiguration(ConfigID = 1547).SetValue(Value = @opt_value) -- EMDEventType(x_eet_AlterProc), EMDUniversalClass(x_eunc_Table), src major id, src minor id, src name -- -1 means ignore target stuff, target major id, target minor id, target name, -- # of parameters, 5 parameters EXEC %%System().FireTrigger(ID = 52, ID = 1, ID = @tabid, ID = 0, Value = @ProcName, ID = -1, ID = 0, ID = 0, Value = NULL, ID = 3, Value = @ProcName, Value = @OptionName, Value = @OptionValue, Value = NULL, Value = NULL, Value = NULL, Value = NULL) -- Commit and reconfigure COMMIT TRANSACTION RECONFIGURE WITH OVERRIDE -- RETURN SUCCESS RETURN 0 -- sp_procoption
