CRM Related SQL Queries 1

SQL Queries Version 1

Get Privilege by Role name

select priv.* 

from Privilege priv

join RolePrivileges rolePrivileges on (rolePrivileges.PrivilegeId = priv.PrivilegeId )

join Role role on (rolePrivileges.RoleId = role.RoleId)

where role.Name = 'system administrator' 

Get the Users By RoleName

select sysuser.FirstName,sysuser.DomainName,sysuser.AccessMode, sysuser.ActiveDirectoryGuid,sysuser.CALType, sysuser.*

from SystemUser sysuser

join SystemUserRoles userRoles on (userRoles.SystemUserId = sysuser.SystemUserId )

join Role role on (userRoles.RoleId = role.RoleId )

where role.Name ='system administrator' 

and sysuser.AccessMode=0 and sysuser.CALType=0 and sysuser.IsDisabled=0

Get All Privilege By Role with formatting

SELECT DISTINCT
FilteredRole.name,
EntityView.PhysicalName AS [Entity Name],
EntityView.IsCustomEntity AS [IsCustom],
displayname.Label as[Entity Display Name] ,


CASE Privilege.AccessRight
WHEN 1 THEN 'READ'
WHEN 2 THEN 'WRITE'
WHEN 4 THEN 'APPEND'
WHEN 16 THEN 'APPENDTO'
WHEN 32 THEN 'CREATE'
WHEN 65536 THEN 'DELETE'
WHEN 262144 THEN 'SHARE'
WHEN 524288 THEN 'ASSIGN'
END AS [Access Level],
CASE PrivilegeDepthMask
WHEN 1 THEN 'User'
WHEN 2 THEN 'Business Unit'
WHEN 4 THEN 'Parent: Child Business Unit'
WHEN 8 THEN 'Organisation'
END AS [Security Level]
FROM  RolePrivileges
INNER JOIN
FilteredRole ON RolePrivileges.RoleId = FilteredRole.roleid
INNER JOIN
PrivilegeObjectTypeCodes ON RolePrivileges.PrivilegeId = PrivilegeObjectTypeCodes.PrivilegeId
INNER JOIN
Privilege ON RolePrivileges.PrivilegeId = Privilege.PrivilegeId
INNER JOIN
EntityView ON EntityView.ObjectTypeCode = PrivilegeObjectTypeCodes.ObjectTypeCode
inner join LocalizedLabelLogicalView displayname
on EntityView.EntityId = displayname.ObjectId
and displayname.ObjectColumnName = 'LocalizedName'
ORDER BY FilteredRole.name, [Entity Name]

Get Picklist values details from an Entity

SELECT DISTINCT e.Name as Entity, a.Name as Attribute, pl.Value, l.Label
FROM MetadataSchema.Attribute a
INNER JOIN MetadataSchema.Entity e
ON a.EntityId = e.EntityId
AND YEAR(e.OverwriteTime) = 1900
INNER JOIN MetadataSchema.AttributeTypes t
ON a.AttributeTypeId = t.AttributeTypeId
AND t.Description = 'picklist'
INNER JOIN MetadataSchema.AttributePicklistValue pl
ON a.OptionSetId = pl.OptionSetId
INNER JOIN MetadataSchema.LocalizedLabel l
ON pl.AttributePicklistValueId = l.ObjectId
where e.Name ='Account'   -- change entity as per reuirement
ORDER BY e.Name, a.Name

Attribute DisplayName

SELECT DISTINCT e.Name as Entity, a.Name as Attribute, pl.Value, l.Label
FROM MetadataSchema.Attribute a
INNER JOIN MetadataSchema.Entity e
ON a.EntityId = e.EntityId
AND YEAR(e.OverwriteTime) = 1900
INNER JOIN MetadataSchema.AttributeTypes t
ON a.AttributeTypeId = t.AttributeTypeId
AND t.Description = 'picklist'
INNER JOIN MetadataSchema.AttributePicklistValue pl
ON a.OptionSetId = pl.OptionSetId
INNER JOIN MetadataSchema.LocalizedLabel l
ON pl.AttributePicklistValueId = l.ObjectId
where e.Name ='Account'   -- change entity as per reuirement
ORDER BY e.Name, a.Name

Difference between 2 organizations 

SELECT  

    T.[name] AS table_name, AC.[name] AS column_name,  

        TY.[name] AS system_data_type,

        Ty.max_length as maxLength into FullCRMSchema_165

FROM    [<DBName>].sys.[tables] AS T  

    INNER JOIN <DBName>.sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 

    INNER JOIN <DBName>.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]

EXCEPT

SELECT  

    T.[name] AS [table_name], AC.[name] AS [column_name],  

        TY.[name] AS system_data_type,

         Ty.max_length as maxLength  into FullCRMSchema_164

FROM    <DBName>.sys.[tables] AS T  

    INNER JOIN <DBName>.sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 

    INNER JOIN <DBName>.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]

    

    

    

    select * from FullCRMSchema_165

    except

    select * from FullCRMSchema_164

Find Present Active Users in CRM who are logged in

SELECT login_name, COUNT(session_id) AS [session_count] 

FROM  sys.dm_exec_sessions

GROUP BY login_name

ORDER BY login_name;

-- Run multiple times

SELECT LTRIM (st.[text]) AS [Command Text],[host_name], der.session_id AS [SPID],

       der.[status], db_name(database_id) AS [Database Name], 

       ISNULL(der.wait_type, 'None') AS [Wait Type], 

       der.logical_reads, der.cpu_time, der.total_elapsed_time 

FROM sys.dm_exec_requests AS der

INNER JOIN sys.dm_exec_connections AS dexc

ON der.session_id = dexc.session_id

INNER JOIN sys.dm_exec_sessions AS dexs

ON dexs.session_id = der.session_id

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

WHERE der.session_id >= 51

AND der.session_id <> @@spid -- eliminate this connection

ORDER BY der.[status];

Find Duplicates records LOGIC only. You should change the table names accordingly

drop table #Temp1

CREATE TABLE #Temp1 (Id int,  Name Char( 30 ), state1 Char( 30 ) )

insert into #Temp1 values ( 50, 'Sujith_ND1','India_ND1');

insert into #Temp1 values ( 51, 'Sujith_ND2','India_ND2');

insert into #Temp1 values ( 52, 'Sujith_ND3','India_ND3');

DECLARE @cnt INT = 0;

Declare @id INT=0

WHILE @cnt < 10

BEGIN

   insert into #Temp1 values ( @id, 'Sujith','India');

   SET @cnt = @cnt + 1;

    SET @id = @id + 1;

END;

set  @cnt  = 0;

WHILE @cnt < 10

BEGIN

   insert into #Temp1 values ( @id, 'Sujith1','India1');

   SET @cnt = @cnt + 1;

     SET @id = @id + 1;

END;

set  @cnt  = 0;

WHILE @cnt < 10

BEGIN

   insert into #Temp1 values ( @id, 'Sujith2','India2');

   SET @cnt = @cnt + 1;

     SET @id = @id + 1;

END;

--select * from #Temp1

---------------------------------------------------

SELECT  id, name,state1,  DENSE_RANK()  over (ORDER BY state1 DESC) as Ranking into #Temp2

FROM #Temp1 

select * from  #Temp2 t join

(select  COUNT(*) as counting,Ranking from  #Temp2 group by ranking having COUNT(*)>1) rankingtable

on t.Ranking=rankingtable.Ranking

Cleanup Async Table:

IF EXISTS (SELECT name from sys.indexes

WHERE name = N'CRM_AsyncOperation_CleanupCompleted')

      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

GO

CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted

ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])

GO

while(1=1)

begin

declare @DeleteRowCount int = 2000

declare @rowsAffected int

declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)

insert into @DeletedAsyncRowsTable(AsyncOperationId)

Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase

where

  OperationType in (1, 9, 12, 25, 27, 10)

  AND StateCode = 3

  AND StatusCode in (30, 32)

select @rowsAffected = @@rowcount

 delete poa from PrincipalObjectAccess poa

   join WorkflowLogBase wlb on

    poa.ObjectId = wlb.WorkflowLogId

   join @DeletedAsyncRowsTable dart on

    wlb.AsyncOperationId = dart.AsyncOperationId

delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d

where

  W.AsyncOperationId = d.AsyncOperationId            

 delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d

where

  B.AsyncOperationId = d.AsyncOperationId

delete BulkDeleteOperationBase From BulkDeleteOperationBase O, @DeletedAsyncRowsTable d

where

  O.AsyncOperationId = d.AsyncOperationId

delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d

where

  WS.AsyncOperationId = d.AsyncOperationID

 delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d

where

  A.AsyncOperationId = d.AsyncOperationId

/*If not calling from a SQL job, use the WAITFOR DELAY*/

if(@DeleteRowCount > @rowsAffected)

  return

else

  WAITFOR DELAY '00:00:02.000'

end

CleanUp a Specific Async Plugin Name

IF EXISTS (SELECT name from sys.indexes

WHERE name = N'CRM_AsyncOperation_CleanupCompleted')

      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

GO

CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted

ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])

GO

while(1=1)

begin

declare @DeleteRowCount int = 2000

declare @rowsAffected int

declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)

insert into @DeletedAsyncRowsTable(AsyncOperationId)

Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase

where

 StateCode in ( 0,20, 21,10)

  and   

-- Use any of the option

--Name='SamplePluginName'

--owningextensionid ='20C25FE6-0F37-E311-AEEA-0050569A063F'

select @rowsAffected = @@rowcount

 delete poa from PrincipalObjectAccess poa

   join WorkflowLogBase wlb on

    poa.ObjectId = wlb.WorkflowLogId

   join @DeletedAsyncRowsTable dart on

    wlb.AsyncOperationId = dart.AsyncOperationId

delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d

where

  W.AsyncOperationId = d.AsyncOperationId            

 delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d

where

  B.AsyncOperationId = d.AsyncOperationId

delete BulkDeleteOperationBase From BulkDeleteOperationBase O, @DeletedAsyncRowsTable d

where

  O.AsyncOperationId = d.AsyncOperationId

delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d

where

  WS.AsyncOperationId = d.AsyncOperationID

 delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d

where

  A.AsyncOperationId = d.AsyncOperationId

/*If not calling from a SQL job, use the WAITFOR DELAY*/

if(@DeleteRowCount > @rowsAffected)

  return

else

  WAITFOR DELAY '00:00:02.000'

end