SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_sys_tables_create_view_without_rowver_computed]
AS 

SET NOCOUNT ON;

DECLARE @min_table_id INT
DECLARE @max_table_id INT 
DECLARE @table_id INT
DECLARE @table_name SYSNAME
DECLARE @schema_name SYSNAME
DECLARE @view_complete_name SYSNAME

DECLARE @tables TABLE(id INT IDENTITY(1,1), table_name SYSNAME, [schema_name] SYSNAME)

declare @sourceControlledObjetsHasData bit = case when exists(select 1 from nm_reference_database_objects) then 1 else 0 end 

INSERT INTO @tables(	table_name,		[schema_name])
SELECT					T.TABLE_NAME,	T.TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES t
	left join nm_reference_database_objects nrdo on nrdo.nrdo_SchemaName = T.TABLE_SCHEMA and nrdo.nrdo_ObjectName = T.TABLE_NAME and nrdo.nrdo_ObjectType = 'U'
WHERE EXISTS(	SELECT 1 
				FROM INFORMATION_SCHEMA.COLUMNS c
				WHERE		t.TABLE_CATALOG = c.TABLE_CATALOG
						AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
						AND t.TABLE_NAME = c.TABLE_NAME
						AND c.DATA_TYPE IN 	('timestamp', 'rowver')
			)
	AND t.TABLE_TYPE = 'BASE TABLE'
	AND LEFT(t.TABLE_NAME,4) IN ('ris_','claim', 'pol_') -- on limite pour le moment le nombre de views
	AND t.TABLE_NAME NOT IN ('ris_cpl', 'pol_cpl', 'act_cpl', 'claim_cpl', 'claim_actors_cpl') -- les tables dynamiques il faut les exclure car elles sont spécifiques à chaque base
	and		(@sourceControlledObjetsHasData = 0 or nrdo.nrdo_ObjectName is not null)

SELECT	@min_table_id = MIN(id),
		@max_table_id = MAX(id)
FROM @tables

IF @min_table_id IS NULL 
BEGIN
	--rien a faire
	RETURN  
END 
ELSE
BEGIN
	SET  @table_id = @min_table_id

	WHILE @table_id <= @max_table_id
	BEGIN

		SELECT	@table_name = t.table_name,
				@schema_name = T.[schema_name]
		FROM @tables t
		WHERE t.id = @table_id

		EXEC p_sys_create_view_without_rowver_computed @schema_name = @schema_name
													  ,@table_name = @table_name
													  ,@view_complete_name = @view_complete_name


		SET @table_id = @table_id + 1

	END
END 

GO
