Thursday, 16 August 2018

MS SQL Server Database Dependence Tree using DMV sys.sql_expression_dependencies

-- By Steven Rao
-- On Aug 16, 2018
-- Given a single database object (such as stored procedure, function, view, table), list the dependance tree view of all depedent objects
-- Recusively looking inside DMV sys.sql_expression_dependencies

-- Based on
-- https://www.sqlservercentral.com/Forums/Topic1874321-3077-1.aspx


declare @schemaName varchar(128), @objectName varchar(128), @objectID int

select @schemaName='dbo', @objectName='sp_SRC_MAIN'
--select @schemaName='dbo', @objectName='mp_MPS53B3_AWD_MAIN'

select @objectID=OBJECT_ID(@objectName)

-- debug and verify ObjectID
--select @schemaName as [@schemaName], @objectName as [@objectName], @objectID as [@objectID]

if object_id('tempdb..#base_objects') is not null
  drop table #base_objects

create table #base_objects
(schemaname varchar(128)
,objectname varchar(128)
,objectID int
)



insert into #base_objects
select @schemaName, @objectName, @objectID


;
--------------------------------------------------------------------------------------
-- extend the expression dependance with a more reliable referenced ID
-- (ignore cross database case)
--------------------------------------------------------------------------------------
with DP as
(
-- referencing_id: parent
-- referenced_id: child
-- need to rely on
-- rely on referencing_id and referenced_entity_name
--select * from sys.sql_expression_dependencies where referencing_id is null or referenced_entity_name is null
--select * from sys.sql_expression_dependencies where referenced_id is null
select
*
-- this could give us more depedence data becasue referenced_id could be null
,isnull(referenced_id, OBJECT_ID(referenced_entity_name)) as ReferencedID
from sys.sql_expression_dependencies
)
-- this is CTE to recursivly serach for object dependence
,
DepTree
  (
  top_level_name, referenced_id, referenced_schema, referenced_name, referencing_id, referencing_schema, referencing_name, NestLevel, callstack, typedesc
  )
as
(
-- initialization of the depedence tree
select
schema_name(o.schema_id) + '.' + o.name as top_level_name
, o.object_id as referenced_id
, schema_name(o.schema_id) as referenced_schema
, o.name as referenced_name
, o.object_id as referencing_id
, schema_name(o.schema_id) as referencing_schema
, o.name as referencing_name
, 0 as NestLevel
, cast ('|' + schema_name(o.schema_id) + '.' + o.name + '|' as nvarchar(max)) as callstack
, o.type_desc as typedesc
from sys.objects o
inner join #base_objects ro
on o.object_id=ro.objectID
--ro.schemaname = schema_name(o.schema_id)
--and ro.objectname = o.name
-- using ID for better matching, this can fix issue with 'sp_SRC_MAIN'
--SCHEMA_ID(ro.schemaname)=o.schema_id
--and OBJECT_ID(ro.objectname) = o.object_id

-- recursive expansion of the depedence tree
union all

SELECT
r.top_level_name
, ref.referenced_id
, ref.referenced_schema
, ref.referenced_name
, ref.referencing_id
, ref.referencing_schema
-- Steven Rao
-- is there a way to show the intermediate parent instead of the top level ??
, ref.referencing_name
--, ref.objectname
, ref.NestLevel
, callstack + ref.objectname + '|' as callstack
, cast(ref.typedesc as nvarchar(60)) as typedesc
FROM  DP d1
  INNER JOIN DepTree r
  ON d1.referencing_id =  r.referenced_id

outer apply (
select
ob2.object_id as referenced_id
, schema_name(ob2.schema_id) as referenced_schema
, ob1.name as referenced_name
, schema_name(ob2.schema_id) + '.' + ob2.name as objectname
, ob1.object_id as referencing_id
, schema_name(ob1.schema_id) as referencing_schema
, ob1.name as referencing_name
, NestLevel + 2 as NestLevel
, cast(ob2.type_desc as nvarchar(60)) as typedesc
from sys.synonyms sy1
inner join sys.objects ob1
on ob1.object_id = sy1.object_id
inner join sys.objects ob2
on '[' + schema_name(ob2.schema_id) + '].[' + ob2.name + ']' = sy1.base_object_name
--OBJECT_ID(ob2.name)=OBJECT_ID(sy1.base_object_name)
where --sy1.object_id = d1.referenced_id
sy1.object_id = d1.ReferencedID


   union all

   select
--d1.referenced_id
d1.ReferencedID as referenced_id
, schema_name(ob1.schema_id) as referenced_schema
, ob1.name as referenced_name
, schema_name(ob1.schema_id) + '.' + ob1.name as objectname
, r.referencing_id
, r.referencing_schema
, r.referencing_name
, NestLevel + 1 as NestLevel
, cast(ob1.type_desc as nvarchar(60)) as typedesc
from sys.objects ob1
where ob1.object_id = d1.ReferencedID

union all

select
--d1.referenced_id
d1.ReferencedID as referenced_id
, schema_name(ty1.schema_id) as referenced_schema
, ty1.name as referenced_name
, schema_name(ty1.schema_id) + '.' + ty1.name as objectname
, r.referencing_id
, r.referencing_schema
, r.referencing_name
, NestLevel + 1 as NestLevel
, cast(d1.referenced_class_desc as nvarchar(60)) as typedesc
from sys.table_types ty1
where ty1.user_type_id = ReferencedID
) ref
  where --------------------------------------------
-- need to aviod infinity loop!!
--------------------------------------------
callstack not like '%|' +  ref.objectname + '|%'
-- can we use not in clasue here??
)

select
NestLevel
  ,referenced_schema as ReferencedSchema
,referenced_name as ReferencedObjectName
-- more user friendly display
-- remove first and last |, then replace | with "<="
,replace(left(right(callstack, len(callstack)-1),len(callstack)-2), '|', ' <= ') as ReferencingStack
,typedesc as ReferencingType
/*
-- already shows up in the referencing stack
--,top_level_name as RootObjectName
,referencing_schema
,referencing_name
,referencing_id
,referenced_id
*/
from DepTree dt
where 1=1
--and NestLevel=0
-- ignore top leve which contains exactly one row for self reference
and NestLevel > 0
-------------------------------------------------------------------------------
-- Case 1
-- Stored Procedure Dependence only
and typedesc in ('SQL_STORED_PROCEDURE')
-------------------------------------------------------------------------------
-- Case 2
-- stored procedure and table dependence
--and typedesc in ('SQL_STORED_PROCEDURE', 'USER_TABLE')
-------------------------------------------------------------------------------
-- Case 3
-- all dependence
--and typedesc like 'SQL%' or typedesc in ('USER_TABLE')
-------------------------------------------------------------------------------
order by 1,2,5,3,4
option (maxrecursion 5000);

Saturday, 11 November 2017

my publication, certification

1) Badges - IBM Big Data University - Cognitive Class


Python for Data Science

IBM Blockchain Essentials

https://www.youracclaim.com/badges/d7cf2864-b2c4-4a24-964e-e076f4f61e01



Machine Learning with R - Level 1


Hadoop Data Access - Level 2

https://www.youracclaim.com/badges/043eea69-78c5-4f64-a272-30dd85f8aa47



Spark - Level 2

https://www.youracclaim.com/badges/8bf56a1d-e26e-4cd5-b86b-cf6c95edd711


Spark - Level 1

https://www.youracclaim.com/badges/3bb9bccc-abb1-4d27-9065-b92e90f3992c


Hadoop Foundations - Level 2

https://www.youracclaim.com/badges/26e8bcfb-2d4f-4032-b643-abdf9c3d2642


Hadoop Data Access - Level 1

https://www.youracclaim.com/badges/c958032b-f950-4a2d-808a-c64c4ea107a6


Hadoop Programming - Level 1

https://www.youracclaim.com/badges/2e7e1c3d-e41c-4200-928c-51f15d3a304a


Hadoop Administration - Level 1

https://www.youracclaim.com/badges/4d136b2a-2ef3-449f-96b2-e608f88b8506


Hadoop Foundations - Level 1

https://www.youracclaim.com/badges/30fbaa56-be31-425b-a48f-1c750dbba2dc


2) Pulications - Simple Talk Publishing -SQL Server Central

Enable/Disable a table to be read only on the fly

http://www.sqlservercentral.com/articles/read+only/100618/


Simple and efficient way to split string with separator

http://www.sqlservercentral.com/scripts/T-SQL/100014/


Dynamically Truncate a Partitioned Table

http://www.sqlservercentral.com/articles/partition+switch/100013/

Saturday, 11 March 2017

MS Dynamics CRM Metadata query sample

The following is a handy query example to look for entity/attribute/option set lookup value:

select
e.Name as Entity
,os.IsGlobal
,a.Name as Attribute
,aop.DisplayOrder
,aop.Value as OptionValue
,ol.Label as OptionLabel
,osl.Label as OptionSetLabel
,al.Label as AttributeLabel
,el.Label as EntityLabel
,e.IsAudited as EntityIsAudited
,os.Name as OptionSet
,e.IsAuditEnabled as EntityIsAuditEnabled
,al.Label as AttributeLable
,a.IsAuditEnabled as AttributeIsAuditEnabled
,atp.Description as AttributType
--,a.*
--,ol.*
--,al.*
from MetadataSchema.Entity e
inner join MetadataSchema.LocalizedLabel el on el.ObjectId=e.EntityId and el.LanguageId=1033 and el.ObjectColumnName='LocalizedName' and year(el.OverwriteTime)=1900
inner join MetadataSchema.Attribute a on a.EntityId=e.EntityId and a.IsCustomField=1 and year(a.OverwriteTime)=1900
inner join MetadataSchema.LocalizedLabel al on al.ObjectId=a.AttributeId and al.LanguageId=1033 and al.ObjectColumnName='DisplayName' and year(al.OverwriteTime)=1900
inner join MetadataSchema.AttributeTypes atp on atp.AttributeTypeId=a.AttributeTypeId
inner join MetaDataSchema.OptionSet os on os.OptionSetId=a.OptionSetId and os.IsCustomOptionSet=1
inner join MetadataSchema.LocalizedLabel osl on osl.ObjectId=os.OptionSetId and osl.LanguageId=1033 and osl.ObjectColumnName='DisplayName' and year(osl.OverwriteTime)=1900
inner join MetadataSchema.AttributePicklistValue aop on aop.OptionSetId=os.OptionSetId and year(aop.OverwriteTime)=1900
inner join MetaDataSchema.LocalizedLabel ol on ol.ObjectId=aop.AttributePicklistValueId and ol.ObjectColumnName='DisplayName' and year(ol.OverwriteTime)=1900 and ol.LanguageId=1033
where year(e.OverwriteTime)=1900
--and e.Name='new_entity'
and e.Name='opportunity'
--and e.IsCustomEntity=1
order by 1,2 desc,3,4

year(...)=1900 can be replaced by =0


Thursday, 2 February 2017

T-SQL to clean up PrincipleObjectAccess table of Microsoft Dynamics CRM

-- https://support.microsoft.com/en-us/help/2664150/how-to-control-principalobjectaccess-table-growth-in-microsoft-dynamics-crm-2011

-- ??
--Delete from SubscriptionTrackingDeletedObject where TimeStamp <= convert(timestamp, 55527527);

BEGIN TRY
BEGIN TRAN t1


IF NOT EXISTS (SELECT * FROM sys.sysobjects
WHERE id = object_id(N'[dbo].[ToDeletePOAEntries]')
AND ObjectProperty(id, N'IsUserTable') = 1)


create table ToDeletePoaEntries
(
ObjectId uniqueidentifier,
Otc int
)


IF NOT EXISTS (SELECT * FROM sys.sysindexes si
INNER JOIN sys.sysobjects so ON si.id = so.id
WHERE so.id = OBJECT_ID(N'[dbo].[ToDeletePoaEntries]')
AND OBJECTPROPERTY(so.id, N'IsUserTable') = 1
AND si.name LIKE '%mainindex%')


CREATE UNIQUE NONCLUSTERED INDEX [mainindex] ON [dbo].[ToDeletePoaEntries]
(
[ObjectId] ASC,
[Otc] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

----- Insert records to be deleted in ToDeletePoaEntries

-- go through all user-owned entities which are not replicated and don't support duplicate detection
declare entity_cursor cursor local FORWARD_ONLY READ_ONLY
for select distinct e.ObjectTypeCode, e.BaseTableName, a.PhysicalName from EntityView e
inner join AttributeView a on e.EntityId = a.EntityId and a.IsPKAttribute = 1
where e.IsReplicated = 0 and e.IsDuplicateCheckSupported = 0 and e.OwnershipTypeMask & 1 = 1

open entity_cursor

declare @baseTableName sysname
declare @otc nvarchar(20)
declare @primaryKey sysname
declare @totalCollected int = 0
declare @currentCollected int

declare @tempRowCount int = 0
declare @collectstatement nvarchar(max)

fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey

while @@FETCH_STATUS = 0
begin
print 'Cleaning up POA for ' + @baseTableName

set @currentCollected = 0



set @collectstatement = 'insert into ToDeletePoaEntries(ObjectId, Otc)
select distinct poa.ObjectId, poa.ObjectTypeCode
from PrincipalObjectAccess poa
left join ' + @baseTableName + ' e on poa.ObjectId = e.' + @primaryKey +
' where e.' + @primaryKey + ' is null and poa.ObjectTypeCode = ' + @otc;

print @collectstatement

exec(@collectstatement)
set @tempRowCount = @@ROWCOUNT
set @currentCollected = @currentCollected + @tempRowCount
print CAST(@currentCollected as nvarchar(20)) + ' records collected for ' + @baseTableName
set @totalCollected = @totalCollected + @currentCollected

fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey
end

close entity_cursor
deallocate entity_cursor

print CAST(@totalCollected as nvarchar(20)) + ' total records collected'

-- Delete query


-- This scripts cleans up orphaned POA records for selected entities

declare @deleteBatchSize int = 50000
declare @deleteBatchSizeNVarChar nvarchar(10) = CAST(@deleteBatchSize as nvarchar(10))
declare @totalDeleted int = 0
declare @currentDeleted int
declare @deletestatement nvarchar(max)

set @currentDeleted = 0
set @tempRowCount = 0

-- delete all records of the current entity type which don't have corresponding object in the base table
set @deletestatement = 'delete top (' + @deleteBatchSizeNVarChar + ') from PrincipalObjectAccess
from PrincipalObjectAccess poa
join ToDeletePoaEntries e on poa.ObjectId = e.ObjectId and poa.ObjectTypeCode = e.Otc'

print @deletestatement

-- delete PrincipalObjectAccess records in batches
exec(@deletestatement)
set @tempRowCount = @@ROWCOUNT
set @currentDeleted = @currentDeleted + @tempRowCount

while @tempRowCount = @deleteBatchSize
begin
exec(@deletestatement)
set @tempRowCount = @@ROWCOUNT
set @currentDeleted = @currentDeleted + @tempRowCount

print CAST(@currentDeleted as nvarchar(20)) + ' records deleted ' + cast(getUtcdate() as nvarchar(50))
--cleanup

end

COMMIT TRAN t1

-- Cleanup

DROP Table [dbo].[ToDeletePoaEntries]
PRINT 'EXECUTION SUCCEED'
END TRY
BEGIN CATCH
ROLLBACK TRAN t1

-- Cleanup

DROP Table [dbo].[ToDeletePoaEntries]

PRINT 'EXECUTION FAILED :' + ERROR_MESSAGE()
END CATCH

T-SQL script to clean up SyncOperationBase table of Microsoft Dynamics CRM

-- https://support.microsoft.com/en-us/help/968520/performance-is-slow-if-the-asyncoperationbase-table-becomes-too-large-in-microsoft-dynamics-crm

/*
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 = 10000
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
;

Saturday, 23 January 2016

SQL Server 2008 discontinued feature


  • backup database with 
  • replace dump with backup
  • replace load with restore
  • backup log with no_log | truncate only not supported, switch to simple recovery mode