C# and I: 06/11/12

June 11, 2012

SettingsPropertyValue and SettingsPropertyValueCollection Extensions

I wrote some Configuration Extension, hope you like em ;)

using System.Configuration;
using System;

namespace PawJershauge.Extensions
{
    namespace Configuration
    {
        /// <summary>
        /// Provides Extension for the Configuration manager
        /// </summary>
        public static class ConfigurationExtensions
        {
            #region SettingsPropertyValue

            /// <summary>
            /// Get the string value of the property
            /// </summary>
            /// <param name="obj">SettingsPropertyValue object to extend the method onto.</param>
            /// <returns>null if value is null; else the string representing tha value.</returns>
            /// <remarks>Developed by Paw Jershauge (Check my Blog fore more C# tips: [C# and I]  Url: http://pawjershauge.blogspot.com)</remarks>
            public static string GetValue(this SettingsPropertyValue obj)
            {
                return obj.PropertyValue == null ? null : obj.PropertyValue.ToString();
            }

            /// <summary>
            /// Get the T value of the property
            /// </summary>
            /// <typeparam name="T">Generic type to get as</typeparam>
            /// <param name="obj">SettingsPropertyValue object to extend the method onto.</param>
            /// <returns>The T value of the property, if the value is null, then default(T) is returned.</returns>
            /// <remarks>Developed by Paw Jershauge (Check my Blog fore more C# tips: [C# and I]  Url: http://pawjershauge.blogspot.com)</remarks>
            public static T GetValue<T>(this SettingsPropertyValue obj) where T : struct
            {
                if (obj.PropertyValue == null)
                    return default(T);
                else
                    return (T)Convert.ChangeType(obj.PropertyValue, typeof(T));
            }

            /// <summary>
            /// Tries to get the T value of the property.
            /// </summary>
            /// <typeparam name="T">Generic type to get as</typeparam>
            /// <param name="obj">SettingsPropertyValue object to extend the method onto.</param>
            /// <param name="value">if return value is true, the out value parameter has the T value of the property</param>
            /// <returns>True if the value is not null, else False.</returns>
            /// <remarks>Developed by Paw Jershauge (Check my Blog fore more C# tips: [C# and I]  Url: http://pawjershauge.blogspot.com)</remarks>
            public static bool TryGetValue<T>(this SettingsPropertyValue obj, out T value) where T : struct
            {
                if (obj.PropertyValue == null)
                {
                    value = default(T);
                    return false;
                }
                else
                {
                    value = (T)Convert.ChangeType(obj.PropertyValue, typeof(T));
                    return true;
                }
            }

            #endregion

            #region SettingsPropertyValueCollection

            /// <summary>
            /// Checks if the collection contains the key.
            /// </summary>
            /// <param name="obj">SettingsPropertyValueCollection object to extend the method onto.</param>
            /// <param name="name">Name of the key to lookup</param>
            /// <returns>true if the key exists else false.</returns>
            /// <remarks>Developed by Paw Jershauge (Check my Blog fore more C# tips: [C# and I]  Url: http://pawjershauge.blogspot.com)</remarks>
            public static bool ContainsKey(this SettingsPropertyValueCollection obj, string name)
            {
                if (obj == null || obj.Count == 0)
                    return false;
                foreach (SettingsPropertyValue item in obj)
                {
                    if (item.Name == name)
                        return true;
                }
                return false;
            }

            /// <summary>
            /// Get the string value of the property
            /// </summary>
            /// <param name="obj">SettingsPropertyValueCollection object to extend the method onto.</param>
            /// <returns>null if value is null; else the string representing tha value.</returns>
            /// <remarks>Developed by Paw Jershauge (Check my Blog fore more C# tips: [C# and I]  Url: http://pawjershauge.blogspot.com)</remarks>
            public static string GetValue(this SettingsPropertyValueCollection obj, string name)
            {
                if (!obj.ContainsKey(name))
                    return null;
                return obj[name].GetValue();
            }

            /// <summary>
            /// Get the T value of the property
            /// </summary>
            /// <typeparam name="T">Generic type to get as</typeparam>
            /// <param name="obj">SettingsPropertyValueCollection object to extend the method onto.</param>
            /// <returns>The T value of the property, if the value is null, then default(T) is returned.</returns>
            /// <remarks>Developed by Paw Jershauge (Check my Blog fore more C# tips: [C# and I]  Url: http://pawjershauge.blogspot.com)</remarks>
            public static T GetValue<T>(this SettingsPropertyValueCollection obj, string name) where T : struct
            {
                if (!obj.ContainsKey(name))
                    return default(T);
                return obj[name].GetValue<T>();
            }

            /// <summary>
            /// Tries to get the T value of the property.
            /// </summary>
            /// <typeparam name="T">Generic type to get as</typeparam>
            /// <param name="obj">SettingsPropertyValueCollection object to extend the method onto.</param>
            /// <param name="value">if return value is true, the out value parameter has the T value of the property</param>
            /// <returns>True if the value is not null, else False.</returns>
            /// <remarks>Developed by Paw Jershauge (Check my Blog fore more C# tips: [C# and I]  Url: http://pawjershauge.blogspot.com)</remarks>
            public static bool TryGetValue<T>(this SettingsPropertyValueCollection obj, string name, out T value) where T : struct
            {
                if (!obj.ContainsKey(name))
                {
                    value = default(T);
                    return false;
                }
                return obj[name].TryGetValue(out value);
            }

            #endregion
        }
    }
}

sp_tableoption (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_tableoption(nvarchar @TableNamePattern
, varchar @OptionName
, varchar @OptionValue)

MetaData:

 create procedure sys.sp_tableoption  
    @TableNamePattern      nvarchar(776)  
   ,@OptionName            varchar(35)  
   ,@OptionValue           varchar(12)  
as  
    --  declare AND INIT VARIABLES  
    declare @OPTbulklock    varchar(25)  
            ,@OPTtextinrow    varchar(25)  
            ,@OPTpintable        varchar(25)  
            ,@OPLargeValOutOfRow varchar(28)  
            ,@OPUseVarDecimal varchar(25)  
            ,@CurrentDBId    int  
            ,@ColId        int  
            ,@TabId        int  
            ,@opt_value int  
            ,@opt_flag    bit  
            ,@permit    bit    --  locked after permission check?  
            ,@ret        int  
  
    select @OptionName = lower(@OptionName collate Latin1_General_CI_AS)  
            ,@OPTbulklock = 'table lock on bulk load'  
            ,@OPTtextinrow = 'text in row'  
            ,@OPTpintable    = 'pintable'  
            ,@OPLargeValOutOfRow = 'large value types out of row'  
            ,@OPUseVarDecimal = 'vardecimal storage format'  
            ,@permit = 1  
            ,@CurrentDBId = db_id()  
  
    --  DISALLOW USER TRANSACTION (except for in 'text in row' and 'large value types out of row' ) --   
    set nocount on  
    set implicit_transactions off  
    if (@@trancount > 0 AND @OptionName = @OPTbulklock)  
    begin  
        raiserror(15002,-1,-1,'sys.sp_tableoption')  
        return @@error  
    end  
  
    --  VALIDATE OPTION VALUE  
    select @opt_value =  
        case when (lower(@OptionValue) in ('1' ,'on' ,'yes' ,'true')) then 1  
            when (lower(@OptionValue) in ('0' ,'off' ,'no' ,'false')) then 0  
            when (@OptionName = @OPTtextinrow AND ISNUMERIC (@OptionValue) <> 0)  
            then convert (int, @OptionValue)  
        end  
  
    --  ERROR if INVALID OPTION NAME OR VALUE  
    if @opt_value is null OR @OptionName is null OR  
        (@OptionName NOT IN (@OPTbulklock, @OPTtextinrow, @OPLargeValOutOfRow, @OPTpintable, @OPUseVarDecimal))  
    begin  
        raiserror(15600,-1,-1, 'sys.sp_tableoption')  
        return @@error  
    end  
  
    --  Return silently when option pintable is specifed. This functionality no longer exists.   
    if (@OptionName = @OPTpintable)  
        return 0  
      
    if (@OptionName = @OPTtextinrow)  
    begin  
        if (@opt_value != 0 and @opt_value != 1 and  
            (@opt_value < 24 or @opt_value > 7000))  
        begin    --  Invalid value  
            raiserror (15112,-1,-1)  
            return @@error  
        end  
    end  
  
    --  vardecimal storage format is locked down in SQL Azure.  
    if (@OptionName = @OPUseVarDecimal and serverproperty('EngineEdition') = 5)  
    begin  
        raiserror(40512,-1,-1,@OptionName)  
        return @@error  
    end  
  
    BEGIN TRANSACTION  
  
    --  VERIFY WE HAVE A USER-TABLE BY THIS NAME IN THE DATABASE  
    select @TabId = object_id from sys.tables  
        where object_id = object_id(@TableNamePattern, 'local')  
  
    if not (@TabId is null)  
    begin  
        --  LOCK TABLE, CHECK STANDARD TABLE-DDL PERMISSIONS  
        EXEC %%Object(MultiName = @TableNamePattern).LockMatchID(ID = @TabId, Exclusive = 1, BindInternal = 0)  
        if @@error <> 0  
            select @permit = 0, @TabId = null  
    end  
  
    if @TabId is null    --  Not found/permission deny  
    begin  
        COMMIT TRANSACTION  
        raiserror(15388,-1,-1,@TableNamePattern)  
        return @@error  
    end  
  
    --  HANDLE TEXT-IN-ROW option  
    if (@OptionName = @OPTtextinrow)  
    begin  
        --  invalidate inrow text pointer for the table  
        --   
        dbcc invalidate_textptr_objid(@TabId)  
        dbcc no_textptr(@TabId, @opt_value)  
    end  
  
    --  HANDLE TABLOCK-ON-BCP option  
    else if (@OptionName = @OPTbulklock)  
    begin  
        --  Make required change  
        if ObjectProperty(@TabId, 'TableIsLockedOnBulkLoad') <> @opt_value  
        begin  
            --  SetLockOnBulkLoad expect bit value  
            select @opt_flag = @opt_value  
            EXEC %%Relation(ID = @TabId).SetLockOnBulkLoad(Value = @opt_flag)  
        end  
    end  
    else if (@OptionName = @OPLargeValOutOfRow)  
    begin  
        --  SetLargeValuesTypeOutOfRow expects bit value  
        select @opt_flag = @opt_value  
        EXEC %%Relation(ID = @TabId).SetLargeValuesTypeOutOfRow(Value = @opt_flag)  
        select @ret = @@error  
        if @ret <> 0  
        begin  
            COMMIT TRANSACTION  
            return @ret  
        end  
    end  
    else if (@OptionName = @OPUseVarDecimal)  
    begin  
        --  SetUseVarDecimal expects bit value.  
        select @opt_flag = convert(bit, @opt_value)  
          
        --  No-op if the property is already in the desired state.  
        if ObjectProperty(@TabId, 'TableHasVarDecimalStorageFormat') <> @opt_flag  
        begin  
            exec %%Relation(ID = @TabId).SetUseVarDecimal(Value = @opt_flag)  
            select @ret = @@error  
            if @ret <> 0  
            begin  
                COMMIT TRANSACTION  
                return @ret  
            end  
        end  
    end  
  
    --  EMDEventType(x_eet_AlterTable), 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 = 22, ID = 1, ID = @TabId, ID = 0, Value = NULL,  
            ID = -1, ID = 0, ID = 0, Value = NULL,   
            ID = 3, Value = @TableNamePattern, Value = @OptionName, Value = @OptionValue, Value = NULL, Value = NULL, Value = NULL, Value = NULL)  
  
    COMMIT TRANSACTION  
  
    --  return success  
    return 0  -- sp_tableoption

sp_tablecollations_100 (Transact-SQL MetaData) Definition

Definition:

sys.sp_tablecollations_100(nvarchar @object)

MetaData:

 create procedure sys.sp_tablecollations_100  
(  
    @object nvarchar(4000)  
)  
as  
    select  
        colid               = s_tcv.colid,  
        name                = s_tcv.name,  
        tds_collation       = s_tcv.tds_collation_100,  
        "collation"         = s_tcv.collation_100  
    from  
        sys.spt_tablecollations_view s_tcv  
    where  
        s_tcv.object_id = object_id(@object, 'local')  
    order by colid

sp_stoppushsubscription_agent (Transact-SQL MetaData) Definition

Definition:

sys.sp_stoppushsubscription_agent(nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, nvarchar @publisher)

MetaData:

 create procedure sys.sp_stoppushsubscription_agent  
(  
    @publication    sysname,  
    @subscriber     sysname,  
    @subscriber_db sysname,  
    @publisher      sysname = null  
)  
as  
begin  
    set nocount on  
    declare @cmd        nvarchar(4000)  
    declare @retcode    int   
    declare @pubtype    sysname  
    declare @stop       tinyint  
      
    set @retcode = 0  
    set @stop = 1  
    exec @retcode = sys.sp_MSrepl_getpublisherinfo  @publisher      = @publisher,  
                                                    @publisher_type = @pubtype OUTPUT,  
                                                    @rpcheader      = @cmd OUTPUT  
    if @retcode <> 0  
        return (@retcode)  
  
    --  Add sp  
    set @cmd = @cmd + N'sys.sp_MSrepl_subscriptionagentjobcontrol'  
  
    exec @retcode = @cmd  
                    @publication = @publication,  
                    @subscriber = @subscriber,  
                    @subscriber_db = @subscriber_db,  
                    @publisher = @publisher,  
                    @publisher_type = @pubtype,  
                    @action = @stop      
    return @retcode  
  
  
end

sp_startpullsubscription_agent (Transact-SQL MetaData) Definition

Definition:

sys.sp_startpullsubscription_agent(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication)

MetaData:

 create procedure sys.sp_startpullsubscription_agent  
(  
    @publisher      sysname,  
    @publisher_db   sysname,  
    @publication    sysname  
)  
as  
begin  
    set nocount on  
    declare @retcode int  
            ,@start tinyint  
  
    set @retcode = 0  
    set @start = 0  
  
    --  Security check  
    exec @retcode = sys.sp_MSreplcheck_subscribe  
    if @@error <> 0 or @retcode <> 0  
        return 1  
  
    exec @retcode = sys.sp_MSpullsubscriptionagentjobcontrol  
        @publisher = @publisher,  
        @publisher_db = @publisher_db,  
        @publication = @publication,  
        @action = @start  
    return @retcode   
end

sp_syspolicy_execute_policy (Transact-SQL MetaData) Definition

Definition:

sys.sp_syspolicy_execute_policy(nvarchar @policy_name
, xml @event_data
, bit @synchronous)

MetaData:

 CREATE PROCEDURE sys.sp_syspolicy_execute_policy   
    @policy_name sysname,  
    @event_data xml,  
    @synchronous bit = 0  
AS  
BEGIN  
  
    IF(DB_NAME() != 'msdb')  
    BEGIN  
        RAISERROR(14416,-1,-1)  
        RETURN 14416  
    END  
  
    --  make sure that the caller is dbo or PolicyAdministratorRole  
    IF ( IS_MEMBER('PolicyAdministratorRole') != 1 AND UPPER(USER_NAME())!= 'DBO')  
    BEGIN  
        RAISERROR(15003, -1, -1, 'PolicyAdministratorRole')  
        RETURN 15003  
    END  
  
    DECLARE @retval int      
    DECLARE @history_id bigint  
    SET @history_id = 0  
  
    EXEC @retval = sys.sp_execute_policy @policy_name, @event_data, @history_id OUTPUT  
  
    IF( (@synchronous = 1) AND (@retval=1))  
    BEGIN  
  
        --  the policy failed so we are going to abort the transaction  
  
        --  make sure we get the log information so that we can recreate   
        --  the log entry  
        DECLARE @policy_id int  
        DECLARE @start_date datetime  
        DECLARE @end_date datetime   
        DECLARE @result bit   
          
        SELECT @policy_id = policy_id,  
                @start_date = start_date,  
                @end_date = end_date,  
                @result = result  
        FROM msdb.dbo.syspolicy_policy_execution_history_internal  
        WHERE history_id = @history_id  
          
        DECLARE @target_query_expression nvarchar(max)  
        DECLARE @execution_date datetime  
        DECLARE @target_result bit  
        DECLARE @result_detail nvarchar(max)  
  
        --  there is only one target  
        SELECT TOP 1 @target_query_expression = target_query_expression,  
               @execution_date = execution_date,  
               @target_result = result,  
               @result_detail = result_detail  
        FROM msdb.dbo.syspolicy_policy_execution_history_details_internal  
        WHERE history_id = @history_id  
  
        ROLLBACK TRANSACTION  
  
        IF( @policy_id IS NOT NULL)  
        BEGIN  
            SET IDENTITY_INSERT msdb.dbo.syspolicy_policy_execution_history_internal ON  
            INSERT INTO msdb.dbo.syspolicy_policy_execution_history_internal(history_id, policy_id, start_date, end_date, result)  
                VALUES (@history_id, @policy_id, @start_date, @end_date, @result)  
            SET IDENTITY_INSERT msdb.dbo.syspolicy_policy_execution_history_internal OFF  
            --  insert empty string because the expression is no longer valid  
            --  once the transaction has been rolled back  
            INSERT INTO msdb.dbo.syspolicy_policy_execution_history_details_internal (history_id, target_query_expression, target_query_expression_with_id, execution_date, result, result_detail)  
                VALUES (@history_id, @target_query_expression, N'', @execution_date, @target_result, @result_detail)  
        END  
    END  
  
    RETURN @retval  
END

sp_statistics_rowset (Transact-SQL MetaData) Definition

Definition:

sys.sp_statistics_rowset(nvarchar @table_name
, nvarchar @table_schema)

MetaData:

   
create procedure sys.sp_statistics_rowset  
(  
    @table_name     sysname,  
    @table_schema   sysname = null  
)  
as  
    select  
        TABLE_CATALOG = db_name(),  
        TABLE_SCHEMA = schema_name(o.schema_id),  
        TABLE_NAME = o.name,  
        CARDINALITY =   convert (int,  
                                (--  Multiple partitions could correspond to one index.  
                                    select sum(rows) from sys.partitions s_p  
                                    where x.object_id = s_p.object_id and x.index_id = s_p.index_id  
                                )  
                                )  
    from  
        sys.all_objects o,  
        sys.indexes x  
    where  
        (  
            (@table_schema is null and o.name = @table_name) or  
            object_id(quotename(@table_schema) + '.' + quotename(@table_name)) = o.object_id  
        ) and  
        o.type in ('U') and  
        x.object_id = o.object_id and  
        x.index_id in (0,1) --  If there are no indexes then table stats are in a row with indid =0  
    order by 2, 3

sp_tables (Transact-SQL MetaData) Definition

Definition:

sys.sp_tables(nvarchar @table_name
, nvarchar @table_owner
, nvarchar @table_qualifier
, varchar @table_type
, bit @fUsePattern)

MetaData:

   
create procedure sys.sp_tables  
(  
    @table_name         nvarchar(384)   = null,  
    @table_owner        nvarchar(384)   = null,  
    @table_qualifier    sysname = null,  
    @table_type         varchar(100) = null,  
    @fUsePattern        bit = 1 --  To allow users to explicitly disable all pattern matching.  
)  
as  
    declare @type1      varchar(3)  
    declare @qual_name  nvarchar(517) --  [schema].[table]  
    declare @table_id   int  
  
    if @table_qualifier = '%' and @table_owner = '' and @table_name = ''  
    begin  
        --  Debug output, do not remove it.  
        --  print 'Special feature #1:  enumerate databases when owner and name are blank but qualifier is explicitly "%".'  
        select  
            TABLE_QUALIFIER = convert(sysname,d.name),  
            TABLE_OWNER     = convert(sysname,null),  
            TABLE_NAME      = convert(sysname,null),  
            TABLE_TYPE      = convert(varchar(32),null),  
            REMARKS         = convert(varchar(254),null)    --  Remarks are NULL.  
        from  
            sys.databases d  
        where  
            d.name <> 'model' --  eliminate MODEL database  
        order by 1  
        return  
    end  
  
    if @table_qualifier = '' and @table_owner = '%' and @table_name = ''  
    begin  
        --  Debug output, do not remove it.  
        --  print 'Special feature #2:  enumerate owners when qualifier and name are blank but owner is explicitly "%".  
        select distinct  
            TABLE_QUALIFIER = convert(sysname,null),  
            TABLE_OWNER     = convert(sysname,schema_name(o.schema_id)),  
            TABLE_NAME      = convert(sysname,null),  
            TABLE_TYPE      = convert(varchar(32),null),  
            REMARKS         = convert(varchar(254),null)    --  Remarks are NULL.  
        from  
            sys.all_objects o  
        where  
            o.type in ('S','U','V')  --  limit columns to tables and views only  
        order by 2  
        return  
    end  
  
    if @table_qualifier = '' and @table_owner = '' and @table_name = '' and @table_type = '%'  
    begin  
        --  Debug output, do not remove it.  
        --  print 'Special feature #3:  enumerate table types when qualifier, owner and name are blank but table type is explicitly "%".'  
        select  
            TABLE_QUALIFIER = convert(sysname,null),  
            TABLE_OWNER     = convert(sysname,null),  
            TABLE_NAME      = convert(sysname,null),  
            TABLE_TYPE      = convert(varchar(32),  
                                        rtrim(substring('SYSTEM TABLETABLE       VIEW',(c.column_id-1)*12+1,12))),  
            REMARKS         = convert(varchar(254),null)    --  Remarks are NULL.  
        from  
            sys.all_objects o,  
            sys.all_columns c  
        where  
            o.object_id = c.object_id and o.object_id = object_id('sysusers') and  
            c.column_id <= 3 --  ISSUE - what is this for ???  
        return  
    end  
  
    --   
    --  End of special features - do normal processing.  
    --   
  
    if @table_qualifier is not null  
    begin  
        if db_name() <> @table_qualifier  
        begin  
            if @table_qualifier = ''  
            begin  --  If empty qualifier supplied, force an empty result set.  
                select @table_name = ''  
                select @table_owner = ''  
            end  
            else  
            begin   --  If qualifier doesn't match current database.  
                raiserror (15250, -1,-1)  
                return  
            end  
        end  
    end  
    select @table_qualifier = null --  it's not needed anymore  
  
    if @table_type is null  
    begin   --  Select all ODBC supported table types.  
        select @type1 = 'SUV'  
    end  
    else  
    begin  
        --  TableType is case sensitive if CS server.  
        if (charindex('''SYSTEM TABLE''',@table_type) <> 0)  
            select @type1 = 'S' --  Add System Tables.  
        else  
            select @type1 = ''  
        if (charindex('''TABLE''',@table_type) <> 0)  
            select @type1 = @type1 + 'U' --  Add User Tables.  
        if (charindex('''VIEW''',@table_type) <> 0)  
            select @type1 = @type1 + 'V' --  Add Views.  
    end  
  
    if @table_name is not null  
    begin  
        if (@table_owner is null) and (charindex('%', @table_name) = 0)  
        begin   --  If owner not specified and table contains wildchar.  
            if exists  
            (  
                select  
                        *  
                from  
                        sys.all_objects o  
                where  
                        o.schema_id = schema_id() and  
                        o.object_id = object_id(@table_name) and  
                        o.type in ('U','V','S')  
            )  
            begin   --  Override supplied owner w/owner of table.  
                select @table_owner = schema_name()  
            end  
        end  
    end  
  
    select @qual_name = isnull(quotename(@table_owner), '') + '.' + quotename(@table_name)  
    select @table_id = object_id(@qual_name)  
  
    if (@fUsePattern = 1) --  Does the user want it?  
    begin  
        if ((isnull(charindex('%', @table_name),0) = 0) and  
            (isnull(charindex('_', @table_name),0) = 0) and  
            (isnull(charindex('%', @table_owner),0) = 0) and  
            (isnull(charindex('_', @table_owner),0) = 0) and  
            (@table_id is not null))  
        begin  
            select @fUsePattern = 0 --  not a single wild char, so go the fast way.  
        end  
    end  
  
    if @fUsePattern = 0  
    begin  
        --  --  Debug output, do not remove it.  
        print '-- -- -- -- -- -- *'  
        print 'There is NO pattern matching.'  
        print @fUsePattern  
        print isnull(@table_name, '@table_name = null')  
        print isnull(@table_owner, '@table_owner = null')  
        print isnull(@table_type, '@table_type = null')  
        print isnull(@type1, '@type1 = null')  
        print '-- -- -- -- -- -- *'  
         --   
        select  
            TABLE_QUALIFIER = convert(sysname,db_name()),  
            TABLE_OWNER     = convert(sysname,schema_name(o.schema_id)),  
            TABLE_NAME      = convert(sysname,o.name),  
            TABLE_TYPE      = convert(varchar(32),  
                                        rtrim(substring('SYSTEM TABLE            TABLE       VIEW       ',  
                                        (ascii(o.type)-83)*12+1,12))  --  'S'=0,'U'=2,'V'=3  
                                     ),  
            REMARKS = convert(varchar(254),null)    --  Remarks are NULL.  
  
        from  
            sys.all_objects o  
  
        where  
            o.object_id = @table_id and  
            o.type in ('S','U','V') and  
            has_perms_by_name(@qual_name, 'object', 'select') = 1 and  
            charindex(substring(o.type,1,1),@type1) <> 0 --  Only desired types.  
        order by 4, 1, 2, 3  
    end  
    else  
    begin  
        --  --  Debug output, do not remove it.  
        print '-- -- -- -- -- -- *'  
        print 'THERE IS pattern matching!'  
        print @fUsePattern  
        print isnull(@table_name, '@table_name = null')  
        print isnull(@table_owner, '@table_owner = null')  
        print isnull(@table_type, '@table_type = null')  
        print isnull(@type1, '@type1 = null')  
        print '-- -- -- -- -- -- *'  
         --   
        select  
            TABLE_QUALIFIER = convert(sysname,db_name()),  
            TABLE_OWNER     = convert(sysname,schema_name(o.schema_id)),  
            TABLE_NAME      = convert(sysname,o.name),  
            TABLE_TYPE      = convert(varchar(32),  
                                        rtrim(substring('SYSTEM TABLE            TABLE       VIEW       ',  
                                              (ascii(o.type)-83)*12+1,  
                                              12))  --  'S'=0,'U'=2,'V'=3  
                                     ),  
            REMARKS = convert(varchar(254),null)    --  Remarks are NULL.  
  
        from  
            sys.all_objects o  
  
        where  
            o.type in ('S','U','V') and  
            has_perms_by_name(quotename(schema_name(o.schema_id)) + '.' + quotename(o.name),  
                              'object',  
                              'select') = 1 and  
            charindex(substring(o.type,1,1),@type1) <> 0 and --  Only desired types.  
            (@table_name  is NULL or o.name like @table_name) and  
            (@table_owner is NULL or schema_name(o.schema_id) like @table_owner)  
        order by 4, 1, 2, 3  
    end

sp_tablecollations_90 (Transact-SQL MetaData) Definition

Definition:

sys.sp_tablecollations_90(nvarchar @object)

MetaData:

   
create procedure sys.sp_tablecollations_90  
(  
    @object nvarchar(4000)  
)  
as  
    select  
        colid               = s_tcv.colid,  
        name                = s_tcv.name,  
        tds_collation       = s_tcv.tds_collation_90,  
        "collation"         = s_tcv.collation_90  
    from  
        sys.spt_tablecollations_view s_tcv  
    where  
        s_tcv.object_id = object_id(@object, 'local')  
    order by colid

sp_tablecollations (Transact-SQL MetaData) Definition

Definition:

sys.sp_tablecollations(nvarchar @object)

MetaData:

   
create procedure sys.sp_tablecollations  
(  
    @object nvarchar(4000)  
)  
as  
    select  
        colid               = s_tcv.colid,  
        name                = s_tcv.name,  
        tds_collation       = s_tcv.tds_collation_28,  
        "collation"         = s_tcv.collation_28  
    from  
        sys.spt_tablecollations_view s_tcv  
    where  
        s_tcv.object_id = object_id(@object, 'local')  
    order by colid

sp_table_validation (Transact-SQL MetaData) Definition

Definition:

sys.sp_table_validation(nvarchar @table
, smallint @rowcount_only
, nvarchar @owner
, tinyint @full_or_fast
, bit @shutdown_agent
, nvarchar @table_name
, nvarchar @column_list)

MetaData:

 create procedure sys.sp_table_validation  
(  
    @table sysname,                --  table name or sync object name  
    @expected_rowcount bigint = NULL OUTPUT,  
    @expected_checksum numeric = NULL OUTPUT,  
    @rowcount_only smallint = 1,  
    --    
    The @rowcount_only param is overloaded for shiloh release due to backward compatibility concerns.  
    In shiloh, the checksum functionality has changed.   So 7.0 subscribers will have the old checksum   
    routines, which generate different CRC values, and do not have functionality for vertical partitions,  
    or logical table structures where column offsets differ (due to ALTER TABLEs that DROP and ADD columns).  
  
    In 7.0, this was a bit column.  0 meant do not do just a rowcount - do a checksum.  1 meant just do a   
    rowcount.  
  
    For Shiloh, this parameter is changed to a smallint.  The name "rowcount_only" is now a   
    misnomer given the overloaded functionality.  It is really a "type of check requested"  
    parameter.   But, the old name is retained for backward compatibility.   It can take   
    these values:  
  
    0 - Do a 7.0 compatible checksum  
    1 - Do a rowcount check only (remains the default)  
    2 - Use new Shiloh checksum functionality.    
  
    Note that because 7.0 subscribers will   
    take this parameter as a bit type, not a smallint, it will be interpreted as simply  
    ON.  That means that passing a 2, and having a 7.0 subscriber, will result in the 7.0  
    subscriber doing only rowcount validation.   The Shiloh subscribers will do both  
    rowcount and checksum.  If you want 7.0 subscribers to do checksum validation, use   
    the value of 0 for this parameter.   Shiloh subscribers can do the 7.0 compatible   
    checksum, but that checksum has the same 7.0 limitations for vertical partitions   
    and differences in physical table structure.)  
  
     --   
  
    @owner    sysname = NULL,  
    @full_or_fast tinyint = 2,        --  full (value 0) does COUNT(*)   
                        --  fast (value 1) uses sysindexes.rowcnt if table (not view);   
                        --  conditional fast (VALUE 2) , first tries fast method, but  
                        --  reverts to full if fast method shows differences.  
    @shutdown_agent bit = 0,         --  If 1 will raise error 20578, which will signal replication agent to shutdown  
    @table_name sysname    = NULL,        --  table name of sync object or the table name for output message  
    @column_list nvarchar(max) = NULL     --  the list of columns - that way the user can skip computed columns etc  
)  
as  
begin  
    declare @retcode int  
    exec @retcode = sys.sp_MStable_validation  
        @table = @table,  
        @expected_rowcount = @expected_rowcount output,  
        @expected_checksum = @expected_checksum output,  
        @rowcount_only = @rowcount_only,  
        @owner = @owner,  
        @full_or_fast = @full_or_fast,  
        @shutdown_agent = @shutdown_agent,  
        @table_name = @table_name,  
        @column_list = @column_list  
    return @retcode  
end

sp_table_types_rowset (Transact-SQL MetaData) Definition

Definition:

sys.sp_table_types_rowset(nvarchar @table_name
, nvarchar @table_schema)

MetaData:

   
create procedure sys.sp_table_types_rowset  
(  
    @table_name     sysname = null,  
    @table_schema   sysname = null  
)  
as  
    select  
        TABLE_CATALOG   = s_tr.TABLE_CATALOG,  
        TABLE_SCHEMA    = s_tr.TABLE_SCHEMA,  
        TABLE_NAME      = s_tr.TABLE_NAME,  
        TABLE_TYPE      = s_tr.TABLE_TYPE,  
        TABLE_GUID      = s_tr.TABLE_GUID,  
        DESCRIPTION     = s_tr.DESCRIPTION,  
        TABLE_PROPID    = s_tr.TABLE_PROPID,  
        DATE_CREATED    = s_tr.DATE_CREATED,  
        DATE_MODIFIED   = s_tr.DATE_MODIFIED  
    from  
        sys.spt_table_types_view s_tr  
    where  
        (@table_name is null or s_tr.TABLE_NAME = @table_name) and  
        (@table_schema is null or schema_id(@table_schema) = s_tr.schema_id)  
    order by 4, 2, 3

sp_table_types (Transact-SQL MetaData) Definition

Definition:

sys.sp_table_types(nvarchar @table_name
, nvarchar @table_owner
, nvarchar @table_qualifier
, varchar @table_type
, bit @fUsePattern)

MetaData:

   
create procedure sys.sp_table_types  
(  
    @table_name         nvarchar(384)   = null,  
    @table_owner        nvarchar(384)   = null,  
    @table_qualifier    sysname = null,  
    @table_type         varchar(100) = null,  
    @fUsePattern        bit = 1  
)  
as  
    --  @table_type and @fUsePattern are intentionally unused parameters  
    --  so that the procedure parameters for sp_table_types match the  
    --  parameters for sp_tables.  
    --   
    if @table_qualifier is not null  
    begin  
        if db_name() <> @table_qualifier  
        begin  
            if @table_qualifier = ''  
            begin  --  If empty qualifier supplied, force an empty result set.  
                select @table_name = ''  
                select @table_owner = ''  
            end  
            else  
            begin   --  If qualifier doesn't match current database.  
                raiserror (15250, -1,-1)  
                return  
            end  
        end  
    end  
    select @table_qualifier = null --  it's not needed anymore  
  
    select  
        TABLE_QUALIFIER = convert(sysname,db_name()),  
        TABLE_OWNER     = convert(sysname,schema_name(tt.schema_id)),  
        TABLE_NAME      = convert(sysname,tt.name),  
        TABLE_TYPE      = convert(varchar(32),'TABLE TYPE'),  
        REMARKS = convert(varchar(254),null)    --  Remarks are NULL.  
    from  
        sys.table_types tt  
  
    where  
        (@table_name  is NULL or tt.name like @table_name) and  
        (@table_owner is NULL or schema_name(tt.schema_id) like @table_owner)  
    order by 4, 1, 2, 3

sp_table_type_primary_keys_rowset (Transact-SQL MetaData) Definition

Definition:

sys.sp_table_type_primary_keys_rowset(nvarchar @table_name
, nvarchar @table_schema)

MetaData:

   
create procedure sys.sp_table_type_primary_keys_rowset  
(  
    @table_name         sysname = null,  
    @table_schema       sysname = null  
)  
as  
    select  
        TABLE_CATALOG   = s_pkv.TABLE_CATALOG,  
        TABLE_SCHEMA    = s_pkv.TABLE_SCHEMA,  
        TABLE_NAME      = s_pkv.TABLE_NAME,  
        COLUMN_NAME     = s_pkv.COLUMN_NAME,  
        COLUMN_GUID     = s_pkv.COLUMN_GUID,  
        COLUMN_PROPID   = s_pkv.COLUMN_PROPID,  
        ORDINAL         = s_pkv.ORDINAL,  
        PK_NAME         = s_pkv.PK_NAME  
    from  
        sys.spt_table_type_primary_keys_view s_pkv  
    where  
        (@table_name is null or s_pkv.TABLE_NAME = @table_name) and  
        (@table_schema is null or schema_id(@table_schema) = s_pkv.schema_id)  
    order by 2, 3

sp_table_type_columns_100_rowset (Transact-SQL MetaData) Definition

Definition:

sys.sp_table_type_columns_100_rowset(nvarchar @table_name
, nvarchar @table_schema
, nvarchar @column_name)

MetaData:

   
create procedure sys.sp_table_type_columns_100_rowset  
(  
    @table_name     sysname = NULL,  
    @table_schema   sysname = NULL,  
    @column_name    sysname = NULL  
)  
as  
    select  
        TABLE_CATALOG           = s_cv.TABLE_CATALOG,  
        TABLE_SCHEMA            = s_cv.TABLE_SCHEMA,  
        TABLE_NAME              = s_cv.TABLE_NAME,  
        COLUMN_NAME             = s_cv.COLUMN_NAME,  
        COLUMN_GUID             = s_cv.COLUMN_GUID,  
        COLUMN_PROPID           = s_cv.COLUMN_PROPID,  
        ORDINAL_POSITION        = s_cv.ORDINAL_POSITION,  
        COLUMN_HASDEFAULT       = s_cv.COLUMN_HASDEFAULT,  
        COLUMN_DEFAULT          = s_cv.COLUMN_DEFAULT,  
        COLUMN_FLAGS            = s_cv.COLUMN_FLAGS,  
        IS_NULLABLE             = s_cv.IS_NULLABLE,  
        DATA_TYPE               = s_cv.DATA_TYPE,  
        TYPE_GUID               = s_cv.TYPE_GUID,  
        CHARACTER_MAXIMUM_LENGTH= s_cv.CHARACTER_MAXIMUM_LENGTH,  
        CHARACTER_OCTET_LENGTH  = s_cv.CHARACTER_OCTET_LENGTH,  
        NUMERIC_PRECISION       = s_cv.NUMERIC_PRECISION,  
        NUMERIC_SCALE           = s_cv.NUMERIC_SCALE,  
        DATETIME_PRECISION      = s_cv.DATETIME_PRECISION,  
        CHARACTER_SET_CATALOG   = s_cv.CHARACTER_SET_CATALOG,  
        CHARACTER_SET_SCHEMA    = s_cv.CHARACTER_SET_SCHEMA,  
        CHARACTER_SET_NAME      = s_cv.CHARACTER_SET_NAME,  
        COLLATION_CATALOG       = s_cv.COLLATION_CATALOG,  
        COLLATION_SCHEMA        = s_cv.COLLATION_SCHEMA,  
        COLLATION_NAME          = s_cv.COLLATION_NAME,  
        DOMAIN_CATALOG          = s_cv.DOMAIN_CATALOG,  
        DOMAIN_SCHEMA           = s_cv.DOMAIN_SCHEMA,  
        DOMAIN_NAME             = s_cv.DOMAIN_NAME,  
        DESCRIPTION             = s_cv.DESCRIPTION,  
        COLUMN_LCID             = s_cv.COLUMN_LCID,  
        COLUMN_COMPFLAGS        = s_cv.COLUMN_COMPFLAGS,  
        COLUMN_SORTID           = s_cv.COLUMN_SORTID,  
        COLUMN_TDSCOLLATION     = s_cv.COLUMN_TDSCOLLATION,  
        IS_COMPUTED             = s_cv.IS_COMPUTED,  
        SS_XML_SCHEMACOLLECTION_CATALOGNAME = s_cv.SS_XML_SCHEMACOLLECTION_CATALOGNAME,  
        SS_XML_SCHEMACOLLECTION_SCHEMANAME  = s_cv.SS_XML_SCHEMACOLLECTION_SCHEMANAME,  
        SS_XML_SCHEMACOLLECTIONNAME         = s_cv.SS_XML_SCHEMACOLLECTIONNAME,  
        SS_UDT_CATALOGNAME      = s_cv.SS_UDT_CATALOGNAME,  
        SS_UDT_SCHEMANAME       = s_cv.SS_UDT_SCHEMANAME,  
        SS_UDT_NAME             = s_cv.SS_UDT_NAME,  
        SS_UDT_ASSEMBLY_TYPENAME= s_cv.SS_UDT_ASSEMBLY_TYPENAME,  
        SS_IS_SPARSE            = s_cv.SS_IS_SPARSE,  
        SS_IS_COLUMN_SET        = s_cv.SS_IS_COLUMN_SET  
  
    from  
        sys.spt_table_type_columns_view s_cv  
  
    where  
        (@table_name is null or s_cv.TABLE_NAME = @table_name) and   
        (@table_schema is null or schema_id(@table_schema) = s_cv.schema_id) and  
        (@column_name is null or s_cv.COLUMN_NAME = @column_name)  
  
    order by 1, 2, 3, 7

   
create procedure sys.sp_table_type_pkeys  
(  
    @table_name      sysname,  
    @table_owner     sysname = null,  
    @table_qualifier sysname = null  
)  
as  
    if @table_qualifier is not null  
    begin  
        if db_name() <> @table_qualifier  
        begin   --  If qualifier doesn't match current database  
            raiserror (15250, -1,-1)  
            return  
        end  
    end  
  
    select  
        TABLE_QUALIFIER  = s_pkv.TABLE_CATALOG,  
        TABLE_OWNER      = s_pkv.TABLE_SCHEMA,  
        TABLE_NAME       = s_pkv.TABLE_NAME,  
        COLUMN_NAME      = s_pkv.COLUMN_NAME,  
        KEY_SEQ          = s_pkv.ORDINAL,  
        PK_NAME          = s_pkv.PK_NAME  
    from  
        sys.spt_table_type_primary_keys_view s_pkv  
    where  
        (@table_name is null or s_pkv.TABLE_NAME = @table_name) and  
        (@table_owner is null or schema_id(@table_owner) = s_pkv.schema_id)  
    order by 1, 2, 3, 5

   
create procedure sys.sp_table_privileges_rowset2  
(  
    @table_schema       sysname = null,  
    @grantor            sysname = null,  
    @grantee            sysname = null  
)  
as  
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -  
--  copy & pasted from version 1 of the SProc and removed checks for 1st parameter !  
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -  
    select  
        GRANTOR         = s_tpv.GRANTOR,  
        GRANTEE         = s_tpv.GRANTEE,  
        TABLE_CATALOG   = s_tpv.TABLE_CATALOG,  
        TABLE_SCHEMA    = s_tpv.TABLE_SCHEMA,  
        TABLE_NAME      = s_tpv.TABLE_NAME,  
        PRIVILEGE_TYPE  = s_tpv.PRIVILEGE_TYPE,  
        IS_GRANTABLE    = s_tpv.IS_GRANTABLE  
    from  
        sys.spt_table_privileges_view s_tpv  
    where  
        (@table_schema is null or @table_schema = s_tpv.TABLE_SCHEMA) and  
        (@grantee is null or @grantee = s_tpv.GRANTEE) and  
        (@grantor is null or @grantor = s_tpv.GRANTOR)  
    order by 4,5,6,1,2

June 11, 2012

Total Pageviews

Where i go...