SQL to Extract Data Porters

SQL to extract Data Porters - useful for impact analysis

with cte_soln as (SELECT [SolutionName] AS [Solution Name]
, CASE 
  WHEN [ComponentName] is not null THEN [ComponentName]
  ELSE RuleBuilderName 
 END AS [Component Name]
, CASE 
  WHEN [ComponentName] is not null THEN [ComponentType]
  WHEN RuleBuilderName is not null THEN 'RuleBuilder'
 END AS [Component Type]
, [Description]
, [xml]
FROM (
 SELECT nref.query('.') as [xml],
 nref.value('../../../Name[1]', 'nvarchar(250)') [SolutionName],
 nref.value('ProcessSpecification[1]/ProcessName[1]', 'nvarchar(500)') [ComponentName],
 nref.value('ProcessSpecification[1]/ComponentType[1]', 'nvarchar(500)') [ComponentType],
 nref.value('RuleBuilderName[1]', 'nvarchar(500)') RuleBuilderName,
 nref.value('Description[1]', 'nvarchar(4000)') [Description]
 FROM CADIS_SYS.CO_SOLUTION CROSS APPLY DEFINITION.nodes('//Content/Items/list/Item') AS R(nref)
 WHERE OBSOLETE=0 ) VW)
select [Solution Name] as SolutionName,[Component Name] as ComponentName
 ,[Component Type] as ComponentType
-- , Definition as [DataPorter]
, row_number() over (partition by [Solution Name], [Component Name] order by  [Solution Name], [Component Name] ) as seq
,dpref.value('SourceDataSource[1]/Select[1]/Text[1]','nvarchar(max)') SourceSQL
,dpref.value('Custom[1]/SelectedProc[1]/Name[1]','nvarchar(200)') SelectedProcess
,dpref.value('DeleteDataSource[1]/Table[1]/Name[1]','nvarchar(200)') DeleteTable
,dpref.value('Email[1]/Subject[1]','nvarchar(200)') EmailSubject
,dpref.value('External[1]/FileName[1]','nvarchar(200)') ExecutableLocation
,dpref.value('SourceFile[1]/FileName[1]','nvarchar(200)') as 'OutputFile'
from cte_soln
left outer join CADIS_SYS.DP_DATAPORT as comps on comps.Name = [Component Name]
CROSS APPLY DEFINITION.nodes('//CadisXml/Content/Inputs/list/Item') AS DP(dpref)
where [solution name] like '%'
and dpref.value('SourceFile[1]/FileName[1]','nvarchar(200)') is null

SQL to extract Cadis Constructors

SQL to extract Cadis Constructor SQL

with cte_soln as (SELECT [SolutionName] AS [Solution Name]
, CASE 
  WHEN [ComponentName] is not null THEN [ComponentName]
  ELSE RuleBuilderName 
 END AS [Component Name]
, CASE 
  WHEN [ComponentName] is not null THEN [ComponentType]
  WHEN RuleBuilderName is not null THEN 'RuleBuilder'
 END AS [Component Type]
, [Description]
, [xml]
FROM (
 SELECT nref.query('.') as [xml],
 nref.value('../../../Name[1]', 'nvarchar(250)') [SolutionName],
 nref.value('ProcessSpecification[1]/ProcessName[1]', 'nvarchar(500)') [ComponentName],
 nref.value('ProcessSpecification[1]/ComponentType[1]', 'nvarchar(500)') [ComponentType],
 nref.value('RuleBuilderName[1]', 'nvarchar(500)') RuleBuilderName,
 nref.value('Description[1]', 'nvarchar(4000)') [Description]
 FROM CADIS_SYS.CO_SOLUTION CROSS APPLY DEFINITION.nodes('//Content/Items/list/Item') AS R(nref)
 WHERE OBSOLETE=0 ) VW)
select 
    [Solution Name] as SolutionName
    ,[Component Name] as ComponentName
    ,[Component Type] as ComponentType
    , row_number() over (partition by [Solution Name], [Component Name] order by  [Solution Name], [Component Name] ) as seq
 --, Definition as [DataPorter]
,Content.value('(Text)[1]', 'varchar(max)') as SourceSQL
,NULL SelectedProcess
,NULL DeleteTable
,NULL EmailSubject
,NULL ExecutableLocation
,NULL as 'OutputFile'
from cte_soln
left outer join CADIS_SYS.DC_CONSTRUCTION as comps on comps.Name = [Component Name]
CROSS APPLY [DEFINITION].nodes('/CadisXml/Content/Inputs/list/Item/RowSource/Select') AS Tbl(Content)
where [solution name] like '%'
and [component type] = 'DataConstructor'

SQL to Extract Cadis Database Parameters from the XML Config

declare @xmlParams xml

declare @name varchar (100)
set @name = '%%'
IF  OBJECT_ID(N'[tempdb]..[#temp]') is not null
drop table #temp

CREATE TABLE #temp
(Name nvarchar(200)
,DefinitionXml xml
,CRC nchar(1000)
,details image
 )
 insert #temp exec CADIS_SYS.SPCO_GLOBALSELBYNAME @Name=N'Database Parameters'
select @xmlParams = DefinitionXml from #temp

-- select * from cadis_sys.co_global

-- select @xmlParams

select T.col.value('(Name)[1]','nvarchar(200)') as name
,T.col.value('Value[1]','nvarchar(2000)') as Value
-- ,T.col.query('.')
from @xmlParams.nodes('/CadisXml/Content/Globals/nv/list/Item') T(col)
where T.col.value('(Name)[1]','nvarchar(200)')  like @name
or T.col.value('Value[1]','nvarchar(2000)') like @name
order by 1

select T.col.value('(Name)[1]','nvarchar(200)') as name
,T.col.value('Path[1]','nvarchar(2000)') as NamePath
,T.col.value('UNCPath[1]','nvarchar(2000)') as UncPath
-- ,T.col.query('.')
from @xmlParams.nodes('/CadisXml/Content/Locs/list/Item') T(col)
where T.col.value('(Name)[1]','nvarchar(200)') like @name
or T.col.value('Path[1]','nvarchar(2000)') like @name

select T.col.value('(Name)[1]','nvarchar(2000)') as ConnectionName
,T.col.value('(Info/Description)[1]','nvarchar(2000)') as Description
,T.col.value('(Info/DataSource)[1]','nvarchar(2000)') as DataSource
,T.col.value('(Info/Type)[1]','nvarchar(2000)') as ConnectionType
-- ,T.col.query('.')
from @xmlParams.nodes('/CadisXml/Content/Conns/list/Item') T(col)
where T.col.value('(Name)[1]','nvarchar(2000)')  like @name
or T.col.value('(Info/Description)[1]','nvarchar(2000)') like @name



drop table #temp