本文转载自：http://www.mscrmsolution.com/blogs/post/CRM-2015-OOTB-Stored-Procedures/
Dynamic CRM 存储过程介绍
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 |