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

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_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

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_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

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_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

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_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

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_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

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_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

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_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

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_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

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_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

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_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

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_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

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_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

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_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

sp_table_type_pkeys (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_table_type_pkeys(nvarchar @table_name
, nvarchar @table_owner
, nvarchar @table_qualifier)

MetaData:

   
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

sp_table_privileges_rowset2 (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_table_privileges_rowset2(nvarchar @table_schema
, nvarchar @grantor
, nvarchar @grantee)

MetaData:

   
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

Total Pageviews