C# and I

April 17, 2012

sp_cleanup_log_shipping_history (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_cleanup_log_shipping_history(uniqueidentifier @agent_id
, tinyint @agent_type)

MetaData:

 create procedure sys.sp_cleanup_log_shipping_history   
(  
    @agent_id uniqueidentifier     --  primary/secondary ID  
    ,@agent_type tinyint              --  0 = Backup, 1 = Copy, 2 = Restore  
)  
as  
begin  
    set nocount on  
    declare @retcode int  
                ,@agent_idstring sysname  
                ,@history_retention_period int  
                ,@curdate_utc datetime  
                ,@monitor_server sysname  
                ,@monitor_server_security_mode int  
    --   
    --  security check  
    --   
    exec @retcode = sys.sp_MSlogshippingsysadmincheck  
    if (@retcode != 0 or @@error != 0)  
        return 1  
    --   
    --  must be invoked from master db  
    --   
    if (db_name() != N'master')  
    begin  
        raiserror(5001, 16,-1)  
        return 1  
    end  
    --   
    --  validate agent_type  
    --   
    if (@agent_type not in (0,1,2))  
    begin  
        raiserror(21055, 16, -1, '@agent_type','sp_MSadd_log_shipping_error_detail')  
        return 1  
    end  
    --   
    --  validate agent  
    --   
    if (sys.fn_MSvalidatelogshipagentid(@agent_id, @agent_type) = 0)  
    begin  
        select @agent_idstring = cast(@agent_id as sysname)  
        raiserror(32016, 16, 1, @agent_idstring, @agent_type)  
        return 1  
    end  
    --   
    --  get monitor server information  
    --   
    if (@agent_type = 0)  
    begin  
        select @monitor_server = pd.monitor_server  
                ,@monitor_server_security_mode = pd.monitor_server_security_mode  
                ,@history_retention_period = mp.history_retention_period  
        from msdb.dbo.log_shipping_primary_databases as pd join msdb.dbo.log_shipping_monitor_primary as mp  
            on pd.primary_id = mp.primary_id  
        where pd.primary_id = @agent_id  
    end  
    else  
    begin  
        --   
        --  there can be multiple secondaries for a secondary primary  
        --  with different history retention periods - choose the highest  
        --   
        select @monitor_server = s.monitor_server  
                ,@monitor_server_security_mode = s.monitor_server_security_mode  
                ,@history_retention_period = max(ms.history_retention_period)  
        from msdb.dbo.log_shipping_secondary as s join msdb.dbo.log_shipping_monitor_secondary as ms  
            on s.secondary_id = ms.secondary_id  
        where s.secondary_id = @agent_id  
        group by s.monitor_server, s.monitor_server_security_mode  
    end  
    --   
    --  cleanup now  
    --   
    select @curdate_utc = getutcdate()  
    exec @retcode = sys.sp_MSprocesslogshippingretentioncleanup   
                    @agent_id = @agent_id  
                    ,@agent_type = @agent_type  
                    ,@monitor_server = @monitor_server  
                    ,@monitor_server_security_mode = @monitor_server_security_mode  
                    ,@history_retention_period = @history_retention_period  
                    ,@curdate_utc = @curdate_utc  
    if (@retcode != 0 or @@error != 0)  
        goto UNDO      
    --   
    --  all done  
    --   
    return 0  
      
UNDO:  
    return 1  
end

sp_cleanmergelogfiles (Transact-SQL MetaData) Definition

Definition:

sys.sp_cleanmergelogfiles(nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @web_server
, int @id)

MetaData:

 create procedure sys.sp_cleanmergelogfiles(  
    @publication            sysname = '%',        --  Publication name  --   
    @subscriber             sysname = '%',        --  Subscriber server  --   
    @subscriber_db          sysname = '%',        --  Subscription database  --   
    @publisher              sysname = '%',        --  Publisher server  --   
    @publisher_db           sysname = '%',        --  Publisher database  --   
    @web_server                sysname = '%',          --  logs from IIS server  --   
    @id                     int = -1              --  id of the log file to get data for  --   
    )AS  
  
    SET NOCOUNT ON  
  
    --   
    --  Declarations.  
     --   
  
    declare @retcode            int  
    declare @pubid                uniqueidentifier  
    declare @subid                uniqueidentifier  
  
    IF object_id('sysmergesubscriptions') is NULL  
        RETURN (0)  
      
    --  Security check  --   
    EXEC @retcode = sys.sp_MSreplcheck_pull @publication = @publication,   
        @raise_fatal_error = 0  
    if @@ERROR <> 0 or @retcode <> 0  
        return(1)  
  
    --   
    --  Parameter Check: @publisher  
    --  Check to make sure that the publisher is defined  
     --   
    IF @publisher <> '%'  
    BEGIN  
        EXECUTE @retcode = sys.sp_validname @publisher  
        IF @@ERROR <> 0 OR @retcode <> 0  
            RETURN (1)  
    END  
  
    --   
    --  Parameter Check:     @subscriber.  
    --  If remote server, limit the view to the remote server's subscriptions.  
    --  Make sure that the name isn't NULL.  
     --   
    if @subscriber IS NULL  
        BEGIN  
            RAISERROR (14043, 16, -1, '@subscriber', 'sp_cleanmergelogfiles')  
            RETURN (1)  
        END  
  
    --   
    --  Parameter Check:     @subscriber.  
    --  Check if remote server is defined as a subscription server, and  
    --  that the name conforms to the rules for identifiers.  
     --   
  
    if @subscriber <> '%'  
        BEGIN  
            EXECUTE @retcode = sys.sp_validname @subscriber  
  
            if @retcode <> 0 OR @@ERROR <> 0  
                RETURN (1)  
  
        END  
  
    --   
    --  Parameter Check:     @publication.  
    --  If the publication name is specified, check to make sure that it  
    --  conforms to the rules for identifiers and that the publication  
    --  actually exists.     Disallow NULL.  
     --   
    if @publication IS NULL  
        BEGIN  
            RAISERROR (14043, 16, -1, '@publication', 'sp_cleanmergelogfiles')  
            RETURN (1)  
        END  
  
    delete mlf  
        FROM  dbo.MSmerge_log_files mlf,  
              dbo.sysmergesubscriptions        subs,  
              dbo.sysmergepublications    pubs  
            where ( @id = -1 or @id = mlf.id)  
                and pubs.pubid = subs.pubid  
                and subs.pubid <> subs.subid  
                and pubs.name =     @publication   
                and mlf.subid = subs.subid  
                and mlf.pubid = subs.pubid  
                and ((@web_server = N'%') or (mlf.web_server = @web_server collate database_default))  
                and ((@subscriber_db = N'%') or (subs.db_name = @subscriber_db collate database_default))  
                and ((@publisher_db = N'%') or (pubs.publisher_db = @publisher_db collate database_default))  
                and ((@subscriber = N'%') or (UPPER(subs.subscriber_server) = UPPER(@subscriber) collate database_default))   
                and ((@publisher = N'%') or (UPPER(pubs.publisher) = UPPER(@publisher) collate database_default))  
          
    return @retcode

sp_clean_db_free_space (Transact-SQL MetaData) Definition

Definition:

sys.sp_clean_db_free_space(nvarchar @dbname
, int @cleaning_delay)

MetaData:

   
create proc sys.sp_clean_db_free_space (@dbname sysname, @cleaning_delay int = 0)  
as  
begin  
    SET NOCOUNT ON  
    declare @quoted_dbname nvarchar(258)  
    set @quoted_dbname=QUOTENAME(@dbname)  
    --   
    --  Parameter check  
    --  @dbname  
    --   
    if (db_id(@dbname) is null)  
    begin  
        RAISERROR(15010, 16, -1, @quoted_dbname)  
        return (1)  
    end  
  
    --   
    --  security check  
    --  only db_owner can execute this  
    --   
    declare @check nvarchar(1024)  
    set @check = 'USE ' + @quoted_dbname +  
    'if (is_member (''db_owner'') != 1)   
    begin  
        raiserror(14260, 16, -1)  
    end'  
    exec (@check)  
    if @@error =14260  
    begin  
        return (1)  
    end  
  
    create table #cleanfiles (fileid int)  
    declare @sql as nvarchar(1024)  
    set @sql = 'insert #cleanfiles (fileid) select file_id from ' + @quoted_dbname + '.sys.database_files where type = 0'  
    exec (@sql)  
  
    declare @file int  
    set @file = 1  
    while @file is not null  
    begin  
        exec sp_clean_db_file_free_space @dbname, @file, @cleaning_delay  
  
        select @file = min(fileid) from #cleanfiles where fileid > @file  
    end  
  
    drop table #cleanfiles  
  
    return (0)  
end

sp_clean_db_file_free_space (Transact-SQL MetaData) Definition

Definition:

sys.sp_clean_db_file_free_space(nvarchar @dbname
, int @fileid
, int @cleaning_delay)

MetaData:

   
create procedure sys.sp_clean_db_file_free_space (  
    @dbname sysname,  
    @fileid int,  
    @cleaning_delay int = 0)  
as  
begin  
    SET NOCOUNT ON  
    declare @quoted_dbname nvarchar(258)  
    set @quoted_dbname=QUOTENAME(@dbname)  
  
    declare @flush nvarchar(1024)  
    set @flush =  'USE ' + @quoted_dbname +  ' checkpoint'  
    --   
    --  Parameter check  
    --  @dbname  
    --   
    if (db_id(@dbname) is null)  
    begin  
        RAISERROR(15010, 16, -1, @quoted_dbname)  
        return (1)  
    end  
  
    --   
    --  security check  
    --  only db_owner can execute this  
    --   
    declare @check nvarchar(1024)  
    set @check = 'USE ' + @quoted_dbname +  
    'if (is_member (''db_owner'') != 1)   
    begin  
        raiserror(14260, 16, -1)  
    end'  
    exec (@check)  
    if @@error =14260  
    begin  
        return (1)  
    end  
  
    declare @page int  
    set @page = 0  
    declare @dbid int  
  
    create table #continueclean (  
        fileid int,  
        page int)  
    declare @sql as nvarchar(1024)  
    set @sql = 'insert #continueclean (fileid, page) select file_id, size from ' + @quoted_dbname + '.sys.database_files where file_id=' + cast(@fileid as nvarchar) + ' and type=0'  
    exec (@sql)  
  
    select @dbid=db_id(@dbname)  
    while exists(select * from #continueclean where fileid = @fileid and page > @page)  
    begin  
        dbcc cleanpage(@dbid , @fileid, @page)  
        set @page = @page+1  
  
        --  delay execution to throttle impact on system  
        if (@cleaning_delay > 0)  
            waitfor delay @cleaning_delay  
  
        --  checkpoint periodically   
        if @page % 10000 = 0  
        begin  
            exec (@flush)  
        end  
  
        set @sql = 'update #continueclean set fileid=file_id, page=size from ' + @quoted_dbname + '.sys.database_files where file_id=' + cast(@fileid as nvarchar) + ' and type=0'  
        exec (@sql)  
    end  
    drop table #continueclean  
    exec (@flush)  
    end

   
CREATE PROCEDURE sys.sp_checkOraclepackageversion  
(  
    @publisher          sysname,  
    @versionsmatch      int OUTPUT,  
    @packageversion        nvarchar(256) OUTPUT   
)  
AS  
BEGIN  
    set nocount on  
    DECLARE @cmd nvarchar(4000)  
                ,@retcode int  
                ,@publisher_type sysname  
  
    EXEC @retcode = sys.sp_MSrepl_getpublisherinfo  
        @publisher        = @publisher,  
        @rpcheader        = @cmd OUTPUT,  
        @publisher_type   = @publisher_type OUTPUT,  
        @skipSecurityCheck=1  
    IF @retcode <> 0  
        RETURN (@retcode)  
  
    --  Reject unsupported publisher types  
    IF @publisher_type NOT IN (N'ORACLE', N'ORACLE GATEWAY')  
    BEGIN  
        RAISERROR (21645, 16, -1, @publisher_type)  
        RETURN (1)  
    END                                                                       
  
    SELECT @publisher = UPPER(@publisher)   
            ,@cmd = @cmd + N'sys.sp_MSrepl_checkOraclepackageversion'  
      
    EXEC @retcode = @cmd  
                    @publisher,  
                    @versionsmatch OUTPUT,  
                    @packageversion OUTPUT  
  
    RETURN (@retcode)  
END

 create procedure sys.sp_checkinvalidivarticle   
    @mode tinyint = 1 --  0 upgrade, 1 snapshot  
    ,@publication sysname = NULL  
as  
begin  
    set nocount on   
      
    if @mode = 0  
    begin   
        IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0) AND  
        (ISNULL(IS_SRVROLEMEMBER('dbcreator'),0) = 0) AND  
        (ISNULL(IS_MEMBER('db_owner'),0) = 0)  
        BEGIN  
            RAISERROR(18799, 16, -1)   
            RETURN (1)  
        END  
    end  
      
    declare @artname nvarchar(524)  
                ,@objname nvarchar(524)  
                ,@pubname nvarchar(524)  
                ,@object_id int  
                ,@result int  
                  
    --  Only attempt to get an application lock if the caller is   
    --  db_owner as the intention of the lock is for coordinating between  
    --  the snapshot agent (db_owner) and ddl replication (requires db_owner  
    --  via sp_MSprep_exclusive)  
    IF (@mode = 1 and (is_member(N'db_owner') = 1))  
    BEGIN  
    EXEC @result = sys.sp_getapplock @Resource = @publication,   
                @LockMode = N'Shared',   
                @LockOwner = N'Session',   
                @LockTimeout = 0,  
                @DbPrincipal = N'db_owner' --  Note that we already require db_owner because of security check performed at the beginning of the procedure  
        IF @result < 0  
        BEGIN  
            RAISERROR(21385, 16, -1, @publication)  
            RETURN (1)  
        END  
    END  
                  
    declare #hlogbasedarticles cursor local fast_forward for  
        select distinct a.name, object_name(a.objid), p.name, a.objid  
        from dbo.sysarticles a join dbo.syspublications p on a.pubid = p.pubid  
            join syssubscriptions s on s.artid = a.artid   
        where objectproperty(objid, 'IsView') = 1   
            and ((@mode = 1 and p.name = @publication) -- snapshot mode  
                or (@mode = 0 and s.status in (2, 3)))--  0 inactive, 1 subscribed, 2 active, 3 initiated  
  
    open #hlogbasedarticles  
    fetch #hlogbasedarticles into @artname, @objname, @pubname, @object_id  
  
    while (@@fetch_status <> -1)  
    begin              
        --  log-based iv article needs to have active cl index   
        if not exists(select * from sys.indexes where object_id = @object_id   
            and index_id = 1 and is_disabled = 0)  
        begin   
            if (@mode = 0)   
            begin   
                raiserror (21857, 10, 1, @artname, @pubname, @objname)  
                exec sys.sp_MSreinit_article   
                    @publication = @pubname  
                    ,@article = @artname  
                    ,@need_new_snapshot = 1  
                    ,@need_reinit_subscription = 1  
                    ,@force_invalidate_snapshot = 1  
                    ,@force_reinit_subscription = 1  
                    ,@ignore_distributor_failure = 1 --  in case distribtor is not up, we don't want to fail upgrade  
            end  
            else if(@mode = 1)  
            begin  
                raiserror (21858, 16, 1, @artname, @pubname, @objname)  
            end  
        end  
        fetch #hlogbasedarticles into @artname, @objname, @pubname, @object_id  
    end  
    close #hlogbasedarticles   
    deallocate #hlogbasedarticles      
    IF (@mode = 1 and (is_member(N'db_owner') = 1))  
    BEGIN  
        EXEC sys.sp_releaseapplock @Resource = @publication, @LockOwner = N'Session', @DbPrincipal = N'db_owner'  
    END  
    return 0  
end

   
create procedure sys.sp_catalogs_rowset  
(  
    @catalog_name       sysname  
)  
as  
    select  
        CATALOG_NAME    = name,  
        DESCRIPTION     = convert(nvarchar(1),null)  
    from  
        sys.databases  
    where  
        name = @catalog_name and (has_dbaccess(name)=1 OR serverproperty('EngineEdition') = 5)  
    order by 1

 create procedure sys.sp_bindefault    -- - 1996/08/30 20:04  
    @defname nvarchar(776),            --  name of the default  
    @objname nvarchar(776),            --  table or usertype name  
    @futureonly varchar(15) = NULL    --  flag to indicate extent of binding  
as  
    declare @defid int                    --  id of the default to bind  
    declare @futurevalue varchar(15)    --  the value of @futureonly that causes  
                                        --   the binding to be limited  
    declare  
        @vc1            nvarchar(517)  
        ,@tab_id        int  
        ,@col_id        int  
        ,@parent_obj    int  
        ,@colname        sysname  
        ,@xtype            tinyint  
        ,@xusertype        int  
        ,@xtypelen        int  
        ,@cur_default    int  
        ,@schid            int  
  
    declare  
        @UnqualDef            sysname  
        ,@QualDef1            sysname  
        ,@QualDef2            sysname  
        ,@QualDef3            sysname  
        ,@UnqualObj            sysname  
        ,@QualObj1            sysname  
        ,@QualObj2            sysname  
        ,@QualObj3            sysname  
  
    set cursor_close_on_commit    off  
    set nocount            on  
  
    select @futurevalue = 'futureonly'    --  initialize @futurevalue  
  
    --  When a default or rule is bound to a user-defined datatype, it is also  
    --   bound, by default, to any columns of the user datatype that are currently  
    --   using the existing default or rule as their default or rule.  This default  
    --   action may be overridden by setting @futureonly = @futurevalue when the  
    --   procedure is invoked.  In this case existing columns with the user  
    --   datatype won't have their existing default or rule changed.  
  
    --  get name parts --   
    select @UnqualDef = parsename(@defname, 1),  
            @QualDef1 = parsename(@defname, 2),  
            @QualDef2 = parsename(@defname, 3),  
            @QualDef3 = parsename(@defname, 4)  
  
    select @UnqualObj = parsename(@objname, 1),  
            @QualObj1 = parsename(@objname, 2),  
            @QualObj2 = parsename(@objname, 3),  
            @QualObj3 = parsename(@objname, 4)  
  
    if (@UnqualDef is null OR @QualDef3 is not null)  
    begin  
        raiserror(15253,-1,-1,@defname)  
        return (1)  
    end  
  
    if (@UnqualObj is null OR @QualObj3 is not null)  
    begin  
        raiserror(15253,-1,-1,@objname)  
        return (1)  
    end  
  
    -- -- -- -- -- -- -- -- --   Verify database.  
    if ((@QualObj2 is not null and @QualObj1 is null)  
        or (@QualDef2 is not null and @QualDef2 <> db_name()))  
    begin  
        raiserror(15076,-1,-1)  
        return (1)  
    end  
  
    --  Check that the @futureonly argument, if supplied, is correct.  
    if (@futureonly is not null)  
    begin  
        select @futureonly = lower(@futureonly)  
        if (@futureonly <> @futurevalue)  
        begin  
            raiserror(15100,-1,-1)  
            return (1)  
        end  
    end  
  
    BEGIN TRANSACTION  
  
    --  Check to see that the default exists and get its id.  
    select @defid = object_id, @parent_obj = parent_object_id from sys.objects  
            where object_id = object_id(@defname, 'local')  
                and type='D '    --  default object 6  
  
    --  Share lock default so it cannot be dropped  
    if not (@defid is null)  
    begin  
        EXEC %%Object(MultiName = @defname).LockMatchID(ID = @defid, Exclusive = 0, BindInternal = 0)  
        if @@error <> 0  
            select @defid = null  
    end  
  
    if @defid is null  
    begin  
        raiserror(15016,-1,-1,@UnqualDef)  
        goto error_abort_exit  
    end  
  
    if @parent_obj > 0  
    begin  
        raiserror(15050,-1,-1,@defname)  
        goto error_abort_exit  
    end  
  
    --  Try to resolve column first. We need to extract  
    --   and verify the table and column names and make sure the user owns  
    --   the table that is getting the default bound. We also need to ensure  
    --   that we don't overwrite any DRI style defaults.  
    if @QualObj1 is not null  
    begin  
        if (@QualObj2 is not null)  
            select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)  
        else  
            select @vc1 = QuoteName(@QualObj1)  
  
        --  Check that table and column exist  
        select    @tab_id = o.object_id  
            from sys.tables o join sys.columns c  
                on c.object_id = o.object_id  
            where o.object_id = object_id(@vc1,'local')  
                and c.name = @UnqualObj  
                  
         if @tab_id is not null  
        begin  
            declare @is_sparse int  
            declare @is_column_set int  
  
            --  Since binding a default is a schema change, update schema count  
            --   for the object in the sysobjects table.  
            EXEC %%Object(MultiName = @vc1).LockMatchID(ID = @tab_id, Exclusive = 1, BindInternal = 0)  
  
            --  Check again that table and column exist  
            if @@error = 0  
                select    @xtype = system_type_id,  
                        @xtypelen = max_length,  
                        @cur_default = default_object_id,  
                        @is_sparse = is_sparse,  
                        @is_column_set = is_column_set  
                    from sys.columns  
                    where object_id = @tab_id  
                        and name = @UnqualObj  
            if @xtype is null  
            begin  
                raiserror(15148,-1,-1, @objname)  
                goto error_abort_exit  
            end  
  
            --  If the column type is timestamp, varchar(max), nvarchar(max), varbinary(max), disallow the bind.  
            --  If the column is computed, disallow the bind.  
            if (   type_name(@xtype) in ('timestamp', 'xml')   
                or ( type_name(@xtype) in ('varchar', 'nvarchar', 'varbinary')  
                      and @xtypelen = -1 )  
                or ColumnProperty(@tab_id, @UnqualObj, 'IsComputed') = 1  
                or @is_sparse = 1  
                or @is_column_set = 1  
                or @xtype = 240 ) --  CLR UDT   
            begin  
                raiserror(15101,-1,-1)  
                goto error_abort_exit  
            end  
  
            --  If the column category is identity, disallow the bind.  
            --   Defaults can't be bound to identity columns.  
            if 1 = ColumnProperty(@tab_id, @UnqualObj, 'IsIdentity')  
            begin  
                raiserror(15102,-1,-1)  
                goto error_abort_exit  
            end  
  
            --  Check to see if the column was created with or altered  
            --   to have a DRI style default value.  
            if (@cur_default is not null) and exists  
                    (select    *  
                        from    sys.objects o  
                        where    @cur_default     = o.object_id  
                        and        @tab_id            = o.parent_object_id)  
            begin  
                raiserror(15103,-1,-1)  
                goto error_abort_exit  
            end  
  
            EXEC %%ColumnEx(ObjectID = @tab_id, Name = @UnqualObj).SetDefault(ID = @defid)  
              
            --  EMDEventType(x_eet_Bind_Default), EMDUniversalClass(x_eunc_Table), src major id, src minor id, src name  
            --  EMDUniversalClass(x_eunc_Table), target major id, 1 means target name is column, target name,  
            --  # of parameters, 5 parameters  
            EXEC %%System().FireTrigger(ID = 218, ID = 1, ID = @defid, ID = 0, Value = NULL,  
                ID = 1, ID = @tab_id, ID = 1, Value = @UnqualObj, ID = 3,  
                Value = @defname, Value = @objname, Value = @futureonly, Value = NULL, Value = NULL, Value = NULL, Value = NULL)  
            raiserror(15511,-1,-1)  
        end  
    end  
  
    --  We're binding to a user type.  In this case, the @objname  
    --   is really the name of the user datatype.  
    --   When we bind to a user type, any existing columns get changed  
    --   to the new binding unless their current binding is not equal  
    --   to the current binding for the usertype or if they set the  
    --   @futureonly parameter to @futurevalue.  
    if @tab_id is null  
    begin  
  
        --   Get the current default for the datatype.  
        if @QualObj2 is null  
            select @xusertype = user_type_id, @cur_default = default_object_id,  
                    @xtype = system_type_id,  
                    @xtypelen = max_length,  
                    @schid = schema_id  
                    from sys.types  
                    where user_type_id = type_id(@objname)  
                    and is_table_type=0  
  
        --  Ex-lock and check permission  
        if not (@xusertype is null)  
        begin  
            EXEC %%ScalarType(MultiName = @objname).LockMatchID(ID = @xusertype, Exclusive = 1)  
            if (@@error <> 0)  
                select @xusertype = null  
        end  
          
        if @xusertype is null  
        begin  
            raiserror(15148,-1,-1, @objname)  
            goto error_abort_exit  
        end  
              
        if ((type_name(@xtype) in ('varchar', 'nvarchar', 'varbinary') and   
                 @xtypelen = -1)  
            or type_name(@xtype) = 'xml'  
            or @xtype = 240 ) --  CLR UDT   
        begin  
            raiserror(15101,-1,-1)  
            goto error_abort_exit  
        end  
  
        if exists (select * from sys.columns c where user_type_id = @xusertype and c.is_sparse = 1)  
        begin  
            raiserror(33079,-1,-1, @objname)  
            goto error_abort_exit  
        end  
  
        EXEC %%ScalarType(ID = @xusertype).SetDefault(ID = @defid)  
        raiserror(15512,-1,-1)  
  
        --  need the new binding.  
        if isnull(@futureonly, ' ') <> @futurevalue  
        begin  
  
            declare    @cur_tab_id        int  
                    ,@bad_tab_id    int  
  
            select    @cur_tab_id = 0        --  detect table id change for lock schema  
                    ,@bad_tab_id = 0    --  skip bad tables (dropped, etc)  
  
            declare ms_crs_t1 cursor local static for  
                select distinct  
                        c.object_id, c.column_id  
                from     sys.columns c join sys.tables o  
                    on c.object_id = o.object_id  
                where c.user_type_id = @xusertype  
                and    ((c.default_object_id = 0)  
                     or (c.default_object_id = @cur_default))  
                order by c.object_id  
                        for read only  
  
            open ms_crs_t1  
            fetch next from ms_crs_t1 into  
                    @tab_id, @col_id  
  
            while @@fetch_status = 0  
            begin  
                if @cur_tab_id <> @tab_id    --  not same table  
                begin  
                    select @cur_tab_id = @tab_id  
                    select @vc1 = quotename(schema_name(OBJECTPROPERTY(@tab_id,'SchemaId'))) + '.'  
                                + quotename(object_name(@tab_id))  
  
                    EXEC %%Object(MultiName = @vc1).LockMatchID(ID = @tab_id, Exclusive = 1, BindInternal = 0)  
                    if @@error <> 0            --  bad table, eg. removed  
                        select @bad_tab_id = @tab_id  
                end  
  
                if @bad_tab_id <> @tab_id    --  table schema locked  
                begin  
                    --  Column cannot be dropped due to type shared lock  
                    select @colname = COL_NAME(@tab_id, @col_id)  
                    EXEC %%ColumnEx(ObjectID = @tab_id, Name = @colname).SetDefault(ID = @defid)  
                end  
  
                fetch next from ms_crs_t1 into  
                        @tab_id, @col_id  
  
            end  
  
            deallocate ms_crs_t1  
            raiserror(15513,-1,-1)  
        end  
  
        --  EMDEventType(x_eet_Bind_Default), EMDUniversalClass(x_eunc_Table), src major id, src minor id, src name  
        --  EMDUniversalClass(x_eunc_Type), target major id, target minor id, target name,  
        --  # of parameters, 5 parameters  
        EXEC %%System().FireTrigger(ID = 218, ID = 1, ID = @defid, ID = 0, Value = NULL,  
            ID = 6, ID = @xusertype, ID = 0, Value = NULL, ID = 3,  
            Value = @defname, Value = @objname, Value = @futureonly, Value = NULL, Value = NULL, Value = NULL, Value = NULL)  
    end  
  
    --  SUCCESS --   
    COMMIT TRANSACTION  
    return (0)  
  
error_abort_exit:  
    COMMIT TRANSACTION  
    return 1    --  sp_bindefault

   
create procedure [sys].[sp_cdc_help_change_data_capture]                    
(                                                          
    @source_schema sysname = null,  
    @source_name sysname = null  
)                                                          
as  
begin  
    set nocount on  
      
    declare @retcode int,  
        @capture_cnt int  
          
    --  Verify database is currently enabled for change data capture  
    if ([sys].[fn_cdc_is_db_enabled]() != 1)  
    begin  
        declare @db_name sysname  
        set @db_name = db_name()  
        raiserror(22901, 16, -1, @db_name)  
        return 1  
    end  
      
    create table #capture_instances  
    (  
        capture_instance sysname collate database_default null,  
        object_id int null  
    )  
      
    set @source_schema = rtrim(@source_schema)  
    set @source_name   = rtrim(@source_name)  
      
    --  Get a list of potential capture instances.   
    exec @retcode = sys.sp_cdc_get_capture_instances @source_schema, @source_name  
      
    if @retcode <> 0  
    begin  
        return 1  
    end              
  
    --  Eliminate from the list any entries that the caller is not  
    --  authorized to access  
    delete from #capture_instances  
    where sys.fn_cdc_has_select_access(capture_instance) = 0  
      
    --  If there are no elements in the #capture_instances and  
    --  an explicit schema was entered, return error  
    select @capture_cnt = count(*) from #capture_instances  
      
    if (@capture_cnt = 0) and (@source_schema is not null)  
    begin  
        raiserror(22981, 16, -1)  
        return 1   
    end  
      
    --  Return information on capture instances   
    exec sys.sp_cdc_change_data_capture  
  
    return 0  
end

 create procedure [sys].[sp_cdc_disable_db]  
as  
begin  
    declare @retcode int  
        ,@db_name sysname  
    
    --  Verify caller is authorized to disable change data capture for the database   
    if (isnull(is_srvrolemember('sysadmin'),0) = 0)   
    begin  
           raiserror(22902, 16, -1)  
        return 1  
    end  
      
    --  Verify database is currently enabled for change data capture  
    if ([sys].[fn_cdc_is_db_enabled]() != 1)  
    begin  
        set @db_name = db_name()  
        raiserror(22901, 10, -1, @db_name)  
        return 0  
    end  
      
    exec @retcode = sys.sp_cdc_disable_db_internal  
      
    if (@@error <> 0) or (@retcode <> 0)  
    begin  
        return 1  
    end  
      
    return 0  
end

C# and I

April 17, 2012

sp_cleanup_log_shipping_history (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_cleanmergelogfiles (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_clean_db_free_space (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_clean_db_file_free_space (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_checkOraclepackageversion (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_checkinvalidivarticle (Transact-SQL MetaData) Definition

Definition:

MetaData:

April 16, 2012

sp_catalogs_rowset (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_bindefault (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_cdc_help_change_data_capture (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_cdc_disable_db (Transact-SQL MetaData) Definition

Definition:

MetaData:

Total Pageviews

Where i go...