MSCRM Solution Come, Learn and Share

By: Amit Choudhary | November 08, 2015

When you create a CRM Organization/instance, CRM automatically creates an organization database. Along with the Out of the box tables, CRM also create some Stored Procedures. Ever wondered, what are those Stored Procedures up to? What is the role of those Stored Procedures in CRM functioning? Well, below is the list of Stored Procedures and brief description that come along with CRM organization.


Name of Stored Procedure

Description

p_CollectSolutionInternalAncestors

Stored procedure that collects all solution internal ancestors for a given set of components in the #ComponentSet table.

p_RetrieveAllPublishedDependencies

Stored procedure to retrieve all published dependencies for all of a given set of solution components in a particular solution.

p_CollectPrincipals

Supports database driven security.

p_CascadeRevokeAccess

For cascading revoke access

p_PublishMetadata

Publishes metadata in the metadata tables

p_GetPrimaryKeyAttributes

Metadata browser stored procedure.

p_GetForeignKeyAttributes

Metadata browser stored procedure.

p_RetrieveDependenciesForUninstall

Stored procedure to retrieve component dependencies that exist for the solution being uninstalled.

p_CleanupInactiveWorkflowAssemblies

Stored procedure to cleanup rows from PluginType,PluginAssembly tables that correspond to inactive workflows.

fn_EndOfNextMonth

Datetime utilities used by report filters

p_GetAuditDetails

Stored procedure to retrieve the new values for all the attributes of a given audit row

fn_GetChildUserPrincipalsAndTeams

Retrieves child users in the users hierarchy, together with their teams.

p_RetrieveDependenciesForDelete

Stored procedure to retrieve all of the component dependencies when a certain component is being deleted.

fn_NTDayOfWeek

Function used for report view generation.

p_GetTablesForDeletion

Get Tables which have DeletionStateCode

p_GetKeyAttributes

Metadata browser stored procedure.

GetSubsidiaryBusinesses

Supports database driven security.

p_CopyRole

Supports database driven security.

fn_RetrieveAllRoles

Retrieves all roles, team roles and parent root roles for the specified userId

p_DetectUnitLoop

Detects unit loop.

fn_GetFiscalYearCore

Used in executing queries which aggregate by fiscal year.

fn_UTCToTzSpecificLocalTime

Function used for report view generation.

p_GrantAccessBulkForCreate

For bulk grant Access during create

p_RollupByOpportunity

Opportunity Rollup support

fn_POARetrieveMultiple

Supports database driven security.

fn_BeginOfLastSevenDay

Datetime utilities used by report filters

p_GetBusinessHierarchy

Returns business hierarchy.

p_Reindex

Indexing support

p_CreateBusinessClosureCalendar

Creates a business closure calendar.

p_feGetFormXml

Generates form xml.

fn_RetrieveVersionAsFloat

Function to convert a nvarchar .NET version to float.

fn_GetBusinessDataLocalizedLabel

Helper function for getting transactional data localized labels for filtered view

p_DetectUserLoop

Detects user loop.

fn_BeginOfLastWeek

Datetime utilities used by report filters

fn_CollectForCascadeReparent

For collecting all cascaded entities into temp table (Reparent Operation)

fn_GetFiscalPeriodCore

Used in executing queries with fiscal period references.

fn_UTCToTzCodeSpecificLocalTime

Function used for report view generation and aggregate queries.

fn_GetTeamIdsWithUserIdForUser

Function used for report view generation.

p_DetectSubjectLoop

Detects subject loop.

p_DefragIndexes

Indexing support

CascadeCollectionTableCreation

Creates CascadeCollectionTable type table

fn_BeginOfSpecificFiscalYear

Datetime utilities used by report filters

fn_CollectForCascadeAssign

For collecting all cascaded entities into temp table (Assign Operation)

fn_CheckAccessForResource

Helper function to see if a given user has acccess to a given resource.

p_DeleteCustomActivityEntries

Stored procedure used to Delete custom activity entries from activity pointer and activity parties.

fn_BeginOfLastFiscalPeriod

Datetime utilities used by report filters

fn_NextXMonth

Datetime utilities used by report filters

fn_BeginOfThisWeek

Datetime utilities used by report filters

p_KeyLeftAttributes

Metadata browser stored procedure.

fn_EndOfNextXFiscalYear

Datetime utilities used by report filters

p_FixUsersForBusinessMove

Supports database driven security.

p_GetAddedRoleCount

Gets number of roles added.

fn_GetMaxPrivilegeDepthMask

Supports database driven security in filtered views.

p_RetrieveDependenciesForExport

Stored procedure to retrieve all component dependencies that are required on an export.

p_RetrieveRoots

Stored procedure that retrieves all of roots for a given component.

fn_CollectForCascadeDelete

For collecting all cascaded entities (Delete Operation)

fn_CollectForCascadeShare

For collecting all cascaded entities into temp table (Share Operation)

fn_LastXYear

Datetime utilities used by report filters

p_DeleteMarketingListMembers

Stored procedure to delete members from a Marketing List using XML.

p_GetAllTableRelationships

Get all table relationships.

p_CreateDefaultCalendar

Creates a default calendar for system users.

fn_RptBracket

Function used for reporting neglected cases.

p_AddAttributeToIndex

Add Attributes to Index

fn_EndOfNextYear

Datetime utilities used by report filters

p_PrincipalAttributeAccessMapReinit

Supports database driven security: reinit PrincipalAttributeAccessMap for a principal user or team.

fn_CollectForCascadeDeleteSchema

For collecting all cascaded entities (Delete Entity Defition Operation)

p_SetDbCollation

Set database collation.

fn_EndOfLastFiscalYear

Datetime utilities used by report filters

fn_BeginOfLastXWeek

Datetime utilities used by report filters

p_InsertNewWebResource

Inserts a new webresource along with its dependencies during DBUpdates.

fn_BeginOfFiscalYear

Datetime utilities used by report filters

fn_LastXMonth

Datetime utilities used by report filters

p_UpdateRootSite

Stored procedure to update root site collection of given SharePoint record.

p_AccountDistRollup

rollup stored procedure for Account Distribution Report

fn_FindUserGuid

Function used for report view generation.

p_RetrievePostsAndComments

Stored procedure to retrieve posts with comments for activity feeds wall given a set of Post Ids.

fn_CollectForCascadeWrapper

For collecting all cascaded and remove link entities (Delete Operation)

p_CascadeGrantAccess

For cascading grant Access

fn_EndOfThisWeek

Datetime utilities used by report filters

fn_CollectForCascadeUnshare

For collecting all cascaded entities into temp table (Unshare Operation)

fn_GetChildUserPrincipals

Retrieves child users in the users hierarchy

p_GrantInheritedAccess

Supports database driven security.

p_DetectAccountLoop

Detects account loop.

p_PopulateDefaultSyncFilters

Populates default Offline and Outlook filters.

p_CollectPrincipalsInDeepAndReinit

Supports database driven security for role change.

fn_LocalTimeToUTC

Function used for report view generation.

p_GetNextEmailTrackingNumber

Generates email tracking number.

p_ResetGoalSubtreeOnDelete

Stored procedure to reset tree id and depth of child goals on goal delete.

p_ReinitRecordCountSnapshots

Reinitializes the record count snapshots table.

fn_GetPrivilegeDepthMask

Supports role upgrade.

p_BusinessUnitMapSetParent

Supports database driven security.

EntityIdCollectionCreation

Creates EntityIdCollectionCreation type table

fn_EndOfTomorrow

Datetime utilities used by report filters

p_GrantUpdateRevokeAttributeAccessSyncTracking

For tracking in synchronization a replicated entity instance on which AttributeAccess is Granted, Updated or Revoked

fn_BeginOfThisMonth

Datetime utilities used by report filters

p_CopyCustomRolesFromParent

Supports database driven security.

fn_EndOfNextXHour

Datetime utilities used by report filters

p_GetTableIntersectRelationships

Metadata browser stored procedure.

p_GetPicklist

Returns picklist information.

fn_BeginOfThisFiscalPeriod

Datetime utilities used by report filters

fn_GetSharedRecordIdsForFilteredView

Retrieves records shared to user.

p_GetEntityPicklists

Returns entity picklist values.

fn_BeginOfThisYear

Datetime utilities used by report filters

p_RetrievePrivileges

Retrieve a set of privileges given a list of privileges ids

p_RollupByAccount

Account Rollup support

fn_GetBusinessUnitFromInnerEntityOfResource

Helper function to get business unit from user or equipment entities.

p_GetNonKeyAttributes

Metadata browser stored procedure.

p_GetTablesReferencedBy

Metadata browser stored procedure.

p_DetectProductKitLoop

Detects product kit loop.

fn_BeginOfNextWeek

Datetime utilities used by report filters

p_GetEntityPrivileges

Returns entity privileges.

p_CollectSolutionInternalDescendents

Stored procedure that retrieves all of the solution internal descendents.

p_SystemUserBuEntityMapReinit

Supports database driven security.

fn_BeginOfLastFiscalYear

Datetime utilities used by report filters

p_DropDefaultConstraint

Drop default constraint

fn_TranslateDateTime

Helper function for regenerating filtered view after customization.

fn_EndOfNextXFiscalPeriod

Datetime utilities used by report filters

p_ReinitPrincipalObjectAccessReadSnapshots

Reinitializes the principal object access read snapshots table.

fn_RollupByOpportunity

Opportunity Rollup support function

p_GetTableRelationships

Metadata browser stored procedure.

p_CascadeCollect

For collecting all cascaded entities into temp table

p_CleanSyncTables

Clean SyncEntry tables

p_DeleteSingleAuditPartition

Stored procedure used to Delete Audit partitions

p_CanRemoveUserFromSysAdmin

Supports database driven security.

p_GetAccessRights

Stored procedure to get access rights on records while adding members to a list.

fn_EndOfThisMonth

Datetime utilities used by report filters

p_BusinessUnitMapAddBusinessUnit

Supports database driven security.

fn_GetFiscalPeriodAndYear

Used in executing queries which aggregate by fiscal period and year.

p_RetrievePostsDataTypes

All the User Defined Table Data types for Post Stored procedures

fn_BeginOfTomorrow

Datetime utilities used by report filters

p_CreateNextAuditPartition

Stored procedure used to create Audit partitions

p_GetTableAttributes

Metadata browser stored procedure.

p_AddSpecialUsers

Supports database driven security.

p_UoMToBase

Supports Product View Report

fn_BeginOfNextFiscalPeriod

Datetime utilities used by report filters

fn_GetFiscalPeriodForCurrentUser

Datetime utilities used by report filters

fn_GetFiscalPeriodCLR

Used in executing queries with fiscal period references.

fn_BeginOfLastXHour

Datetime utilities used by report filters

fn_UserSharedAttributeAccessForObject

Helper function which returns a bit if a user has read access to the given attribute on the given entity instance on via multiple team memberships.

fn_UTCToLocalTime

Function used for report view generation.

fn_EndOfThisYear

Datetime utilities used by report filters

p_ReinitSystemUserManagerMap

Reinitializes the system user manager map table.

fn_UserSharedAttributeAccess

Helper function which returns a single row for each attribute a user has read access on via multiple team memberships.

fn_EndOfSpecificFiscalYear

Datetime utilities used by report filters

fn_EndOfFiscalPeriod

Datetime utilities used by report filters

p_PopulateDefaultFilters

Populates default filters.

p_PrincipalEntityMapReinitBulk

Supports database driven security.

p_RollupByContact

Contact Rollup support

p_CascadeReparent

For cascading reparent

fn_BeginOfFiscalPeriod

Datetime utilities used by report filters

p_BatchDeleteDependencies

Stored procedure to delete a batch of dependencies and dependency nodes.

fn_BeginOfNextFiscalYear

Datetime utilities used by report filters

fn_EndOfNextSevenDay

Datetime utilities used by report filters

fn_NextXYear

Datetime utilities used by report filters

p_PrincipalEntityMapReinit

Supports database driven security.

fn_BeginOfNextYear

Datetime utilities used by report filters

fn_GetSharePointChildLocations

Retrieves all the child SharePoint document locations.

fn_RollupByContact

Contact Rollup support function

fn_BeginOfYear

Datetime utilities used by report filters

p_RetrieveAbsoluteAndSiteCollectionUrl

Stored procedure to retrieve absolute and site collection url for a given record.

fn_GetTeamIdsForUser

Function used for report view generation.

p_SetDbColumnCollation

Set collation for all columns in database.

p_GetNewValueFromAuditTrail

Stored procedure to retrieve new value of a given attribute from the database

p_DetectReportLoop

Detects report loop.

p_RetrieveTopArticlesBySubject

Returns top 10 articles by subject.

fn_BeginOfThisFiscalYear

Datetime utilities used by report filters

p_SystemUserBuEntityMapInitForBuCreate

Supports database driven security.

p_AddMarketingListMembers

Stored procedure to add members to a Marketing List using XML.

p_FreeProcCache

Flushes the stored procedure cache for this database prior to regenerating cascading sprocs

p_RetrieveMultipleDirectAncestors

Stored procedure that collects all direct solution internal ancestors for a given set of components.

fn_GetCustomAttributeIds

Gets a list of all custom attributes which were not shipped out of box

fn_CollectForCascadeRemoveLink

For collecting all remove link entities (Delete Operation)

p_GetStatusValues

Returns status values.

fn_BeginOfToday

Datetime utilities used by report filters

fn_EndOfThisFiscalYear

Datetime utilities used by report filters

fn_EndOfNextXDay

Datetime utilities used by report filters

fn_EndOfLastYear

Datetime utilities used by report filters

p_GetKeyMatchAttributes

Metadata browser stored procedure.

fn_EndOfLastWeek

Datetime utilities used by report filters

fn_GetFormatStrings

Gets the format strings for date, time, numbers and currency

p_DeleteAttributeFromIndexById

Delete an attribute from Index by index id

fn_GetFiscalYear

Used in executing queries which aggregate by fiscal year.

p_CollectPrincipalsForBusinessUnitMove

Supports database driven security.

fn_FirstDayOfMonth

Returns first day of the month of the give day.

p_GetTablesReferencing

Metadata browser stored procedure.

p_ma_DeleteListMembers

Marketing automation stored procedure.

fn_GetFiscalPeriod

Used in executing queries with fiscal period references.

fn_EndOfYesterday

Datetime utilities used by report filters

p_RetrieveTopArticlesByProduct

Returns top 10 articles by product.

fn_BeginOfNextMonth

Datetime utilities used by report filters

fn_BeginOfLastMonth

Datetime utilities used by report filters

p_DetectArbitraryLoop

Detects arbitrary loop.

p_PublishLabelsByObjectId

Publishes just localized labels associated with provided object ids

fn_GetOwnerIdsForFilteredView

Retrieves principals with Basic Read privilege for entity.

p_ReindexAll

Stored procedure used to analyze and re-index indices that have become disorganized.

fn_GetLocalizedLabel

Helper function for getting localized labels for filtered view.

fn_GetMaxUserPrivilege

Helper function to get the maximum privilege a user has for a given role.

p_DeleteAttributeFromAllIndexes

Delete an attribute from all indexes

fn_BeginOfLastXFiscalYear

Datetime utilities used by report filters

fn_TzSpecificLocalTimeToUTC

Function used for report view generation.

p_DeleteAttributeFromIndex

Delete an attribute from Index

fn_EndOfNextXWeek

Datetime utilities used by report filters

p_RetrievePosts

Stored procedure to retrieve posts with comments for activity feeds wall.

p_RetrieveDependenciesForCompatibility

Stored procedure to retrieve all of the component dependencies which would cause compatibility issues for solution export.

fn_GetFiscalYearCLR

Used in executing queries which aggregate by fiscal year.

fn_GetNumberFormatString

Gets the format string for a number or currency

fn_EndOfNextFiscalYear

Datetime utilities used by report filters

p_RetrievePostsOnEntities

Stored procedure to retrieve posts with comments for a given set of records

p_MatchSubjects

Match two subjects: whether subject2 is a high level subject of subject1.

p_InsertMultipleAuditRows

Stored procedure to insert multiple audit rows at a time

p_InitGoalSubTree

Stored procedure to init tree id and depth of child goals on change of parent goal.

p_ChangeRolesForBusinessMove

Supports database driven security.

p_RetrieveMultipleRoots

Stored procedure that retrieves all of roots for a given components.

p_KeyRightAttributes

Metadata browser stored procedure.

p_DetectContactLoop

Detects contact loop.

p_GetAllChildBusinessIds

Returns all child business ids.

fn_GetUtcDateTrunc

Gets the utc time truncated to seconds

p_GetTableForeignKeys

Metadata browser stored procedure.

p_AccountOVRollup

rollup stored procedure for Account Overview Report

fn_EndOfLastMonth

Datetime utilities used by report filters

fn_CollectReportsTree

This function returns the whole tree of reports, given a single report

fn_BeginOfYesterday

Datetime utilities used by report filters

fn_EndOfNextWeek

Datetime utilities used by report filters

p_PopulateSystemUserPrincipals

Supports database driven security.

fn_EndOfToday

Datetime utilities used by report filters

fn_BeginOfDay

Datetime utilities used by report filters

p_ShrinkMirroredFile

DBCC SHRINKFILE for mirrored database.

p_RetrievePrivilegeMaxDepthFromTeamRoles

Computes max depth of all privileges accross all roles assigned to teams in which user has membership.

p_GetTxnSessionToken

Returns transaction token.

fn_CollectReports

Report link collector for reporting platform

p_DetectBusinessLoop

Detects business loop.

p_CanMakeReadOnlyUser

Check if user can be made read only

ObjectTypeCodeIdAttributeCollectionCreation

Creates ObjectTypeCodeIdAttributeCollection type table

p_AccountSummaryRollup

rollup stored procedure for Account Summary Report

fn_FindBusinessGuid

Returns the businessunit id of current user

p_IsInBusinessDeep

Supports database driven security.

fn_BeginOfLastXDay

Datetime utilities used by report filters

p_GetSubjectHierarchy

Returns subject hierarchy.

p_NeglectedAccountRollup

rollup stored procedure for Neglected Accounts Report

fn_GetCutoverTime

Calculates the cutovertime of current year

fn_BeginOfLastXFiscalPeriod

Datetime utilities used by report filters

fn_BeginOfLastYear

Datetime utilities used by report filters

p_RetrieveAbsoluteAndSiteCollectionUrlForS2S

Stored procedure to retrieve absolute url, siteRelativeUrl and site collection url for a given record.

fn_BeginOfMonth

Datetime utilities used by report filters

p_InheritRoles

Supports database driven security.

fn_GetUsersFiscalPeriodAndYear

Datetime utilities used by report filters

p_RetrieveYammerPosts

Stored procedure to retrieve yammer posts.

fn_GetFiscalPeriodAndYearCLR

Used in executing queries which aggregate by fiscal period and year.

p_GetTablePrimaryKey

Metadata browser stored procedure.

fn_GetGuidsFromString

Parsing entity guids into temp table

p_GetDbSize

Gets size of database in KB.

fn_RollupByAccount

Account Rollup support function

fn_BeginOfHour

Datetime utilities used by report filters

fn_BeginOfWeek

Datetime utilities used by report filters

`

Tags

MoCA

MoCA Offline

Unmapped profiles

CRM organization import

Input Parameters

CRM Plugin

Plugin context

Outlook Client

Outlook Client Statistics

Performance

Plugin execution time

Debug Javascript

Debug Javascript in MoCA

Optimistic Concurrency

RowVersion

IfRowVersionMatches

IsOptimisticConcurrencyEnabled

Dynamics CRM 2016

new capabilities in CRM 2016

CRM 2016 Release Preview

What's new in CRM 2016

What's coming in CRM 2016

CRM 2016 Release Notes

CRM Online

CRM On Premise

Online vs On Premise

Online and On Premise comparison

CRM 2015

Stored Procedure

Out of the Box Stored Procedure

OOTB Stored Procedure

OOTB

OOTB SP

SP

Xrm Snippets

CRM JavaScript

JavaScript

Full Text Search

FTS

CRM 2015 UR1

Everything about Full Text Search

Multi Entity Search Performance issue

Enable Full Text Search in CRM

InterviewQuestions

CRM Interview Question

Interview Question

Interview Questions

MSCRM Interview Question

Microsoft CRM Interview Question

Configure columns for Multi Entity Search

Multi Entity Search

Configure View Columns

View Columns for Multi Entity Search

Configure Global Search view

configure view Columns

Dynamics 365

Dynamics 365 Update 1

8 2 1

Data Import

Data Import permission

MSCRM Data Import

MSCRM

MSCRM Import

MSCRM Data Import privileges

MSCRM Data Import Security Role

Business Process Flow

Business Process Flow Enhancements

Business Process Flow with Dynamics 365

CRM 8 2 1 Business Process Flow

Dynamics 365 Business Process Flow

Concurrent Business Process Flow

WebApi

Generic WebApi library for CRM

WebApi for CRM

WebApi library for CRM

CRM

WebApi

Webpi for Dynamics CRM

WebApi for Dynamics 365

Generic library

generic Web Api

Business Process Flow issues

Business Process Flow issues and solution

Dynamics 365 Business Process Flow issues

Upgrade Plugin to Sandbox Mode

Sandboxed

Sandboxed Plugins

Move Plugins to Sandbox Mode

Move Plugins to Sandbox Mode Customization xml

Customization xml edit for Plugin

Move None to Sandbox

Move None to Sandbox CRM Plugin

last login date

last access date

last access time

user access

user access audit

last login time

CRM last login date

CRM last access date

CRM last access time

CRM user access

CRM user access audit

CRM last login time

User Access Online

Last Login CRM Online

Default Business Process Flow

Default Process Flow on Load

Default Business Process Flow On Load

Default Business Process Flow on Create

Default Process flow on Create

ADX Portal

ADX Portal Source code

Microsoft Dynamics 365 Portals

Dynamics 365 Portals

Portals

UnsupportedCode

Unsupported Code

Delete Reports

Delete Reports form CRM

Delete Managed Reports

Delete Out of the Box Reports

Delete CRM Reports

Unsupported CRM Code

Dynamics 365 WebAPI

Dynamics 365 WebAPI update lookup

Update Lookup WebAPI

WebAPI for CRM Update

Update Lookup CRM WebAPI

Update Customer lookup via webapi

Update Regarding lookup via WebAPI