netsi1964 · March 14, 2016 13:23 · netsi1964 · Apr 16, 2013
diff --git a/DynamicwebCMSsqlScripts.sql b/DynamicwebCMSsqlScripts.sql
 -- Dynamicweb CMS sql scripts
 -- By Sten Hougaard, netsi1964@gmail.com
 FOR XML AUTO

 -- Opdateret

 ---------------------------------------------------------e------------------------------
 -- Accessuser/page
 ---------------------------------------------------------------------------------------
 -- Find pages modified by non-admins on given areaid
 p
 SELECT page.PageID,
       AccessUserUserName,
       AccessUserName,
       AccessUserEmail
 FROM page
 JOIN accessuser ON page.PageUserEdit = accessuser.AccessUserID
 WHERE AccessUserType>1
    AND page.pageareaid = 26
    SELECT AccessUserID,
       AccessUserUserName,
       AccessUserName,
       AccessUserEmail
    FROM accessuser

 ----------
 -- Enable alle users
 ----------

 UPDATE accessuser
 SET AccessUserActive = 1 WHERE AccessUserActive = 0
 AND AccessUserType = 5

 ---------------------------------------------------------------------------------------
 -- Page
 ---------------------------------------------------------------------------------------
 -- Find pageids on area where ecomnavigation is active

 SELECT pageid,
       PageNavigation_UseEcomGroups,
       PageNavigationParentType,
       PageNavigationGroupSelector,
       PageNavigationMaxLevels,
       PageNavigationProductPage,
       PageNavigationShopSelector
    FROM page WHERE pageareaid = 26
    AND PageNavigation_UseEcomGroups != 'False'

 ----------------
 ---  ECOM FILTER
 ----------------
 select * from EcomGroupFilterSetting 

 update EcomGroupFilterSetting 
 set EcomGroupFilterSettingVisibility  = 'Prohibit'  
 where EcomGroupFilterSettingGroupID  in ('SHOP1_FJERNBETJE', 'SHOP1_MIKROFON','SHOP1_KIKKERT','SHOP1_REMME', 'SHOP1_ANDET', 'SHOP1_PRINTER', 'SHOP1_FORBRUGSTO' )

 select * from EcomFilterDefinition 
 order by EcomFilterDefinitionName 



 ------
 -- Direct path
 ------
 -- Find urlpaths

    SELECT [UrlPathID] ,
       [UrlPathPath] ,
       [UrlPathActive] ,
       [UrlPathAreaID]
    FROM [danboDW8].[dbo].[UrlPath] WHERE UrlPathPath = 'kontakt'
 ORDER BY UrlPathAreaID

 -- Disable urlpaths

 UPDATE [danboDW8].[dbo].[UrlPath]
 SET [UrlPathActive] = 0
 WHERE UrlPathPath = 'kontakt'

 -- Find order via ordid

    SELECT *
    FROM ecomorders WHERE OrderID = 'ORDER509'
    
 -- find orders which has been completed within the last 14 days
 select OrderID as ordrenummer,
 OrderCustomerName as kundenavn,
 OrderCustomerAddress as  kundeadresse,
 OrderCustomerZip as postnr,
 OrderCustomerCity as kundeby,
 OrderDate as ordredato,
 ecomOrderStates.OrderStateName as ordrestatus,
 concat('http://yourdoma.in/pageWithCartV2.aspx?CompletedOrderId=',OrderID,'&CompletedOrderSecret=',OrderSecret) as OrderLink
 from ecomorders
 join ecomOrderStates on ecomorders.OrderStateID = ecomOrderStates.OrderStateID
 where DATEDIFF(day,orderdate,GETDATE()) <= 14
 and OrderComplete = 1

 -- Find order via customer name

 SELECT VikingOrderNo, OrderID, OrderPriceWithVAT, OrderCustomerEmail, OrderCustomerName, OrderComplete, OrderCustomerAddress, OrderGatewayPaymentStatus, OrderGatewayResult, OrderGatewayTransactionProblems, OrderPaymentMethod FROM ecomorders
 where OrderCustomerName like 'sonja%'

 -- Find number of products with comment
 SELECT count(productname) from COMMENT
 join ecomproducts on CommentItemID = ecomproducts.productid
 where CommentItemType = 'ecomProduct'
 and ecomproducts.productactive = 1


 -- Find salesdiscount

    SELECT *
    FROM EcomSalesDiscount WHERE SalesDiscountProductsAndGroups !='' --where SalesDiscountName = 'DUMMY'

 -- Find groups which a product is related to
 SELECT GroupProductRelationGroupID, *  FROM ecomGroupProductrelation
 where groupproductrelationproductid = 'PROD504'

 -- Find groups used on a given LANGID and SHOPID
 SELECT groupid, groupname FROM EcomGroups
 where Groupid in (
       SELECT distinct groupid FROM EcomGroups WHERE GroupID in 
              (SELECT ShopGroupGroupID FROM EcomShopGrouprelation WHERE ShopGroupShopID = 'SHOP1')
       AND GroupLanguageID = 'LANG1')", shopID, langID)
       

 ---------------------------------------------------------------------------------------
 -- GENERIC  GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC
 ---------------------------------------------------------------------------------------
 -- Find tables in Dynamicweb CMS

 SELECT name
 FROM SysObjects WHERE xtype = 'U'
 and name like '%filter%'
 ORDER BY name

 -- find defined items
 SELECT name
 FROM SysObjects WHERE xtype = 'U'
 and name like 'ItemType_%'
 ORDER BY name

 -- col info about item
 SELECT sys.columns.name,
       sys.types.[name] as coltype, sys.columns.[max_length],
       sys.columns.is_nullable
 FROM sys.columns
 JOIN sys.types ON sys.columns.[system_type_id] = sys.types.[system_type_id]
 WHERE object_id = OBJECT_ID('ItemType_Literature')
 and sys.types.[name] != 'sysname'
 ORDER BY sys.columns.name

 -- Create a list of distinct values and their count
 SELECT Region, count(*) AS count
 FROM itemtype_story
 WHERE Region is not null
 GROUP BY Region



 SELECT *
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME LIKE 'news%'
 ORDER BY [TABLE_NAME]

 -- Get columns info

 SELECT sys.columns.name,
       sys.types.[name] as coltype, sys.columns.[max_length],
       sys.columns.is_nullable
 FROM sys.columns
 JOIN sys.types ON sys.columns.[system_type_id] = sys.types.[system_type_id]
 WHERE object_id = OBJECT_ID('ecomorders')
 and sys.types.[name] != 'sysname'
 ORDER BY sys.columns.name


 -- Find information about table

 SELECT name,
       *
 FROM sys.columns
 WHERE object_id = OBJECT_ID('AccessUser')

 -- Find types of sql

    SELECT *,name, PRECISION, SCALE, max_length
    FROM sys.types
 ORDER BY [system_type_id]

 -----
 -- Paragraph
 --

 SELECT *
 FROM paragraph
 WHERE CAST(ParagraphModuleSystemName AS VARCHAR) LIKE '%NewsV2%'

 -- FIND PARAGRAPHS WITH MODULE NewsV2


 -- Find paragraph module setting from paragraphs which are below a given pageid

    SELECT ParagraphModuleSettings,
       *
    FROM paragraph WHERE paragraphpageid IN
        (SELECT pageid
         FROM page
         WHERE pageparentpageid = 3164)
    AND cast(ParagraphModuleSettings AS varchar(3000)) != ''


 -- Find modules used on paragraph
 SELECT DISTINCT ParagraphModuleSystemName
 FROM paragraph WHERE ParagraphModuleSystemName != ''
 ORDER BY ParagraphModuleSystemName


 -- Find paragraph using a given module
 SELECT * FROM paragraph
 WHERE ParagraphModuleSystemName 
 = 'NewsV2'

 -- Find module settings

 SELECT ParagraphModuleSettings
 FROM paragraph
 WHERE ParagraphModuleSystemName = 'NewsV2'
    FOR XML PATH

 ---------------------------------------------------------------------------------------
 -- STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS
 ---------------------------------------------------------------------------------------
 -- STYLESHEETS: Find names of used stylesheets on soloution

 SELECT StylesheetStylesheetNodename,
       StylesheetStylesheetID
    FROM StylesheetStylesheet WHERE StylesheetStylesheetID IN
        (SELECT DISTINCT PageStylesheet AS StylesheetStylesheetID
         FROM page)
 ORDER BY StylesheetStylesheetNodename

 -- STYLESHEETS: Find names of used stylesheets on AREA

 SELECT StylesheetStylesheetNodename,
       StylesheetStylesheetID
 FROM StylesheetStylesheet
 WHERE StylesheetStylesheetID IN
        (SELECT DISTINCT PageStylesheet AS StylesheetStylesheetID
         FROM page
         WHERE PageAreaID = 52
         OR PageAreaID = 51)
 ORDER BY StylesheetStylesheetNodename

 -- STYLESHEETS: Find a page from specified areaid where page uses a given stylesheetname

 SELECT top(1) StylesheetStylesheet.StylesheetStylesheetNodename,
       PageMenuText,
       PageID
 FROM page
 LEFT JOIN StylesheetStylesheet ON page.PageStylesheet = StylesheetStylesheet.StylesheetStylesheetID
 WHERE StylesheetStylesheet.StylesheetStylesheetNodename LIKE 'STYLESHEETNAME%'
    AND (PageAreaID = 52
         OR PageAreaID = 51)


 -- Denne sql viser navne på de templates der er i brug på aktive templates på en løsning

 SELECT TemplateIsDefault,
       TemplateName,
       TemplateFile,
       TemplateDescription
 FROM TEMPLATE WHERE TemplateId IN
    (SELECT DISTINCT ParagraphTemplateID AS TemplateId
     FROM paragraph
     WHERE ParagraphShowParagraph = 1)
 ORDER BY TemplateName

 --- A list with count of used templates on active paragraphs
 SELECT count(ParagraphTemplate) as used, ParagraphTemplate FROM paragraph
 where ParagraphTemplate  in (
  select distinct ParagraphTemplate from Paragraph
  where ParagraphTemplate != ''
  and ParagraphShowParagraph = 1
  )
 and ParagraphShowParagraph = 1
 group by ParagraphTemplate
 order by used desc

 --- A list with count of used templates on active pages
 SELECT count(PageLayout) as used, PageLayout FROM Page
 where PageLayout  in (
  select distinct PageLayout from Page
  where PageLayout != ''
  and PageActive = 1
  )
 group by PageLayout
 order by used desc

 --- A list with count of used modules on active paragraphs
 SELECT count(ParagraphModuleSystemName) as used,module.ModuleName FROM paragraph
 join module on module.ModuleSystemName = paragraph.ParagraphModuleSystemName
 where ParagraphModuleSystemName  in (
  select distinct ParagraphModuleSystemName from paragraph
  where ParagraphModuleSystemName != ''
  and ParagraphShowParagraph  = 1
  )
 group by module.ModuleName
 order by used desc



 SELECT PageNoindexNofollow,
       PageRobots404,
       *
 FROM page
 WHERE PageAreaID = 1
    UPDATE page
    SET PageNoindexNofollow = 1,
        PageRobots404 = 1 WHERE PageAreaID = 1
    UPDATE page
    SET PageNoindexNofollow = 1,
        PageRobots404 = 1 WHERE PageAreaID = 1

 --- Which layouts are used on soloution
 SELECT DISTINCT PageLayout
 FROM page 
 where Pagelayout is not null and PageLayout != ''
 order by PageLayout

 -- Pages using a specified layout
 SELECT * FROM page 
 where Pagelayout = 'service_v1.html'



 --- Datalist ---
 --- Update connection db name after database copied from one server to another --

    UPDATE DMViewSetting
    SET ViewSettingConnectionDBName = 'liveServerDatabaseName'
    WHERE ViewSettingConnectionDBName = 'developmentServerDatabaseName'
    
 -- Get number of orders in each shop
 SELECT OrderShopId, count(*) AS orders
 FROM ecomorders
 where OrderShopID != ''
 GROUP BY OrderShopID 

 -- get active products with variant
 SELECT top(10) * FROM ecomproducts
 where ProductVariantID  != ''
 and productactive = 1

 -- Get products which have a give product related to them
 select top(10) ProductRelatedProductID  from EcomProductsRelated 
 where ProductRelatedProductRelID  = '108648'

 -- get productvariants
 select productid, ProductVariantID,productlanguageid , productname from ecomproducts
 where productid = '108817'
 and productlanguageid = 'LANG1'
 and productactive = 1


 -- Defined product fields
 select * from EcomProductField 

 -- Defined options on product fields (list type)
 select * from EcomFieldOption 

 -- Defined translations for options on product fields (list type)
 select * from EcomFieldOptionTranslation
	-- Dynamicweb CMS sql scripts
	-- By Sten Hougaard, netsi1964@gmail.com
	FOR XML AUTO

	-- Opdateret

	---------------------------------------------------------e------------------------------
	-- Accessuser/page
	---------------------------------------------------------------------------------------
	-- Find pages modified by non-admins on given areaid
	p
	SELECT page.PageID,
	AccessUserUserName,
	AccessUserName,
	AccessUserEmail
	FROM page
	JOIN accessuser ON page.PageUserEdit = accessuser.AccessUserID
	WHERE AccessUserType>1
	AND page.pageareaid = 26
	SELECT AccessUserID,
	AccessUserUserName,
	AccessUserName,
	AccessUserEmail
	FROM accessuser

	----------
	-- Enable alle users
	----------

	UPDATE accessuser
	SET AccessUserActive = 1 WHERE AccessUserActive = 0
	AND AccessUserType = 5

	---------------------------------------------------------------------------------------
	-- Page
	---------------------------------------------------------------------------------------
	-- Find pageids on area where ecomnavigation is active

	SELECT pageid,
	PageNavigation_UseEcomGroups,
	PageNavigationParentType,
	PageNavigationGroupSelector,
	PageNavigationMaxLevels,
	PageNavigationProductPage,
	PageNavigationShopSelector
	FROM page WHERE pageareaid = 26
	AND PageNavigation_UseEcomGroups != 'False'

	----------------
	--- ECOM FILTER
	----------------
	select * from EcomGroupFilterSetting

	update EcomGroupFilterSetting
	set EcomGroupFilterSettingVisibility = 'Prohibit'
	where EcomGroupFilterSettingGroupID in ('SHOP1_FJERNBETJE', 'SHOP1_MIKROFON','SHOP1_KIKKERT','SHOP1_REMME', 'SHOP1_ANDET', 'SHOP1_PRINTER', 'SHOP1_FORBRUGSTO' )

	select * from EcomFilterDefinition
	order by EcomFilterDefinitionName



	------
	-- Direct path
	------
	-- Find urlpaths

	SELECT [UrlPathID] ,
	[UrlPathPath] ,
	[UrlPathActive] ,
	[UrlPathAreaID]
	FROM [danboDW8].[dbo].[UrlPath] WHERE UrlPathPath = 'kontakt'
	ORDER BY UrlPathAreaID

	-- Disable urlpaths

	UPDATE [danboDW8].[dbo].[UrlPath]
	SET [UrlPathActive] = 0
	WHERE UrlPathPath = 'kontakt'

	-- Find order via ordid

	SELECT *
	FROM ecomorders WHERE OrderID = 'ORDER509'

	-- find orders which has been completed within the last 14 days
	select OrderID as ordrenummer,
	OrderCustomerName as kundenavn,
	OrderCustomerAddress as kundeadresse,
	OrderCustomerZip as postnr,
	OrderCustomerCity as kundeby,
	OrderDate as ordredato,
	ecomOrderStates.OrderStateName as ordrestatus,
	concat('http://yourdoma.in/pageWithCartV2.aspx?CompletedOrderId=',OrderID,'&CompletedOrderSecret=',OrderSecret) as OrderLink
	from ecomorders
	join ecomOrderStates on ecomorders.OrderStateID = ecomOrderStates.OrderStateID
	where DATEDIFF(day,orderdate,GETDATE()) <= 14
	and OrderComplete = 1

	-- Find order via customer name

	SELECT VikingOrderNo, OrderID, OrderPriceWithVAT, OrderCustomerEmail, OrderCustomerName, OrderComplete, OrderCustomerAddress, OrderGatewayPaymentStatus, OrderGatewayResult, OrderGatewayTransactionProblems, OrderPaymentMethod FROM ecomorders
	where OrderCustomerName like 'sonja%'

	-- Find number of products with comment
	SELECT count(productname) from COMMENT
	join ecomproducts on CommentItemID = ecomproducts.productid
	where CommentItemType = 'ecomProduct'
	and ecomproducts.productactive = 1


	-- Find salesdiscount

	SELECT *
	FROM EcomSalesDiscount WHERE SalesDiscountProductsAndGroups !='' --where SalesDiscountName = 'DUMMY'

	-- Find groups which a product is related to
	SELECT GroupProductRelationGroupID, * FROM ecomGroupProductrelation
	where groupproductrelationproductid = 'PROD504'

	-- Find groups used on a given LANGID and SHOPID
	SELECT groupid, groupname FROM EcomGroups
	where Groupid in (
	SELECT distinct groupid FROM EcomGroups WHERE GroupID in
	(SELECT ShopGroupGroupID FROM EcomShopGrouprelation WHERE ShopGroupShopID = 'SHOP1')
	AND GroupLanguageID = 'LANG1')", shopID, langID)


	---------------------------------------------------------------------------------------
	-- GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC
	---------------------------------------------------------------------------------------
	-- Find tables in Dynamicweb CMS

	SELECT name
	FROM SysObjects WHERE xtype = 'U'
	and name like '%filter%'
	ORDER BY name

	-- find defined items
	SELECT name
	FROM SysObjects WHERE xtype = 'U'
	and name like 'ItemType_%'
	ORDER BY name

	-- col info about item
	SELECT sys.columns.name,
	sys.types.[name] as coltype, sys.columns.[max_length],
	sys.columns.is_nullable
	FROM sys.columns
	JOIN sys.types ON sys.columns.[system_type_id] = sys.types.[system_type_id]
	WHERE object_id = OBJECT_ID('ItemType_Literature')
	and sys.types.[name] != 'sysname'
	ORDER BY sys.columns.name

	-- Create a list of distinct values and their count
	SELECT Region, count(*) AS count
	FROM itemtype_story
	WHERE Region is not null
	GROUP BY Region



	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE'
	AND TABLE_NAME LIKE 'news%'
	ORDER BY [TABLE_NAME]

	-- Get columns info

	SELECT sys.columns.name,
	sys.types.[name] as coltype, sys.columns.[max_length],
	sys.columns.is_nullable
	FROM sys.columns
	JOIN sys.types ON sys.columns.[system_type_id] = sys.types.[system_type_id]
	WHERE object_id = OBJECT_ID('ecomorders')
	and sys.types.[name] != 'sysname'
	ORDER BY sys.columns.name


	-- Find information about table

	SELECT name,
	*
	FROM sys.columns
	WHERE object_id = OBJECT_ID('AccessUser')

	-- Find types of sql

	SELECT *,name, PRECISION, SCALE, max_length
	FROM sys.types
	ORDER BY [system_type_id]

	-----
	-- Paragraph
	--

	SELECT *
	FROM paragraph
	WHERE CAST(ParagraphModuleSystemName AS VARCHAR) LIKE '%NewsV2%'

	-- FIND PARAGRAPHS WITH MODULE NewsV2


	-- Find paragraph module setting from paragraphs which are below a given pageid

	SELECT ParagraphModuleSettings,
	*
	FROM paragraph WHERE paragraphpageid IN
	(SELECT pageid
	FROM page
	WHERE pageparentpageid = 3164)
	AND cast(ParagraphModuleSettings AS varchar(3000)) != ''


	-- Find modules used on paragraph
	SELECT DISTINCT ParagraphModuleSystemName
	FROM paragraph WHERE ParagraphModuleSystemName != ''
	ORDER BY ParagraphModuleSystemName


	-- Find paragraph using a given module
	SELECT * FROM paragraph
	WHERE ParagraphModuleSystemName
	= 'NewsV2'

	-- Find module settings

	SELECT ParagraphModuleSettings
	FROM paragraph
	WHERE ParagraphModuleSystemName = 'NewsV2'
	FOR XML PATH

	---------------------------------------------------------------------------------------
	-- STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS
	---------------------------------------------------------------------------------------
	-- STYLESHEETS: Find names of used stylesheets on soloution

	SELECT StylesheetStylesheetNodename,
	StylesheetStylesheetID
	FROM StylesheetStylesheet WHERE StylesheetStylesheetID IN
	(SELECT DISTINCT PageStylesheet AS StylesheetStylesheetID
	FROM page)
	ORDER BY StylesheetStylesheetNodename

	-- STYLESHEETS: Find names of used stylesheets on AREA

	SELECT StylesheetStylesheetNodename,
	StylesheetStylesheetID
	FROM StylesheetStylesheet
	WHERE StylesheetStylesheetID IN
	(SELECT DISTINCT PageStylesheet AS StylesheetStylesheetID
	FROM page
	WHERE PageAreaID = 52
	OR PageAreaID = 51)
	ORDER BY StylesheetStylesheetNodename

	-- STYLESHEETS: Find a page from specified areaid where page uses a given stylesheetname

	SELECT top(1) StylesheetStylesheet.StylesheetStylesheetNodename,
	PageMenuText,
	PageID
	FROM page
	LEFT JOIN StylesheetStylesheet ON page.PageStylesheet = StylesheetStylesheet.StylesheetStylesheetID
	WHERE StylesheetStylesheet.StylesheetStylesheetNodename LIKE 'STYLESHEETNAME%'
	AND (PageAreaID = 52
	OR PageAreaID = 51)


	-- Denne sql viser navne på de templates der er i brug på aktive templates på en løsning

	SELECT TemplateIsDefault,
	TemplateName,
	TemplateFile,
	TemplateDescription
	FROM TEMPLATE WHERE TemplateId IN
	(SELECT DISTINCT ParagraphTemplateID AS TemplateId
	FROM paragraph
	WHERE ParagraphShowParagraph = 1)
	ORDER BY TemplateName

	--- A list with count of used templates on active paragraphs
	SELECT count(ParagraphTemplate) as used, ParagraphTemplate FROM paragraph
	where ParagraphTemplate in (
	select distinct ParagraphTemplate from Paragraph
	where ParagraphTemplate != ''
	and ParagraphShowParagraph = 1
	)
	and ParagraphShowParagraph = 1
	group by ParagraphTemplate
	order by used desc

	--- A list with count of used templates on active pages
	SELECT count(PageLayout) as used, PageLayout FROM Page
	where PageLayout in (
	select distinct PageLayout from Page
	where PageLayout != ''
	and PageActive = 1
	)
	group by PageLayout
	order by used desc

	--- A list with count of used modules on active paragraphs
	SELECT count(ParagraphModuleSystemName) as used,module.ModuleName FROM paragraph
	join module on module.ModuleSystemName = paragraph.ParagraphModuleSystemName
	where ParagraphModuleSystemName in (
	select distinct ParagraphModuleSystemName from paragraph
	where ParagraphModuleSystemName != ''
	and ParagraphShowParagraph = 1
	)
	group by module.ModuleName
	order by used desc



	SELECT PageNoindexNofollow,
	PageRobots404,
	*
	FROM page
	WHERE PageAreaID = 1
	UPDATE page
	SET PageNoindexNofollow = 1,
	PageRobots404 = 1 WHERE PageAreaID = 1
	UPDATE page
	SET PageNoindexNofollow = 1,
	PageRobots404 = 1 WHERE PageAreaID = 1

	--- Which layouts are used on soloution
	SELECT DISTINCT PageLayout
	FROM page
	where Pagelayout is not null and PageLayout != ''
	order by PageLayout

	-- Pages using a specified layout
	SELECT * FROM page
	where Pagelayout = 'service_v1.html'



	--- Datalist ---
	--- Update connection db name after database copied from one server to another --

	UPDATE DMViewSetting
	SET ViewSettingConnectionDBName = 'liveServerDatabaseName'
	WHERE ViewSettingConnectionDBName = 'developmentServerDatabaseName'

	-- Get number of orders in each shop
	SELECT OrderShopId, count(*) AS orders
	FROM ecomorders
	where OrderShopID != ''
	GROUP BY OrderShopID

	-- get active products with variant
	SELECT top(10) * FROM ecomproducts
	where ProductVariantID != ''
	and productactive = 1

	-- Get products which have a give product related to them
	select top(10) ProductRelatedProductID from EcomProductsRelated
	where ProductRelatedProductRelID = '108648'

	-- get productvariants
	select productid, ProductVariantID,productlanguageid , productname from ecomproducts
	where productid = '108817'
	and productlanguageid = 'LANG1'
	and productactive = 1


	-- Defined product fields
	select * from EcomProductField

	-- Defined options on product fields (list type)
	select * from EcomFieldOption

	-- Defined translations for options on product fields (list type)
	select * from EcomFieldOptionTranslation