Dynamic CRM 内置存储过程

本文转载自：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