/*=== lister les applications avec des fichiers contenant des chemins en Dur =============*/
CREATE TABLE
#asm_application_tmp (id INT, ProductName nvarchar(512), ProductCode nvarchar(512), Sources nvarchar(512))
INSERT INTO
#asm_application_tmp
SELECT
application_id, product_name, product_code, original_src_file FROM [dbo].[asm_application]
SELECT DISTINCT
mf.[application_id], a.ProductName, a .ProductCode, mf.[long_filename], mf.[target_path], [value], a.Sources
FROM
[dbo].[asm_fileinfo_property] afp
INNER JOIN
[dbo].[asm_fileinfo] af ON af.[fileinfo_id] = afp.[fileinfo_id]
INNER JOIN
[dbo].[msi_file] mf ON af.[hash] = mf.[file_hash]
INNER JOIN
#asm_application_tmp a ON mf.[application_id] = a.id
WHERE
(
[property_id] = (SELECT property_id FROM [dbo].[asm_property] WHERE [name] = 'Capture absolute paths (UNICODE)')
OR [property_id] = (SELECT property_id FROM [dbo].[asm_property] WHERE [name] = 'Capture absolute paths (ANSI)' )
)
AND
(
RIGHT(mf.[long_filename],4) = '.bat' COLLATE Latin1_General_CI_AS
OR RIGHT(mf.[long_filename],4) = '.cmd' COLLATE Latin1_General_CI_AS
OR RIGHT(mf.[long_filename],4) = '.cfg' COLLATE Latin1_General_CI_AS
)
AND afp.value IS NOT NULL
ORDER BY mf.[application_id]
DROP TABLE #asm_application_tmp