SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure [dbo].[p_sys_database_objects_create_views]
as

-- CATALIN - on utilise plus cette procédure mais on la garde

return

set nocount on;

declare @select_view_name sysname
declare @insert_view_name sysname
declare @table_name sysname
declare @current_id int
declare @db_name nvarchar(128)
declare @new_db_name nvarchar(128)
declare @sql_view_select nvarchar(max)
declare @sql_view_insert nvarchar(max)
declare @min_id int
declare @max_id int
declare @create_select_view bit
declare @create_insert_view bit
declare @points_number int
declare @new_db_exists bit
declare @select_union bit
declare @select_union_all bit


declare @ErrorMessage nvarchar(4000)
declare @ErrorNumber int
declare @ErrorSeverity int
declare @ErrorState int
declare @ErrorLine int 
declare @ErrorProcedure nvarchar(200)

declare @tables table(id int identity(1,1), table_name sysname, create_select_view bit, create_insert_view bit, points_number int, select_union_all bit, select_union bit)

insert into @tables (table_name,					    create_select_view,	   create_insert_view, points_number,	select_union,	 select_union_all)				    
		  select '[input_data_dump_header]',			    1,				   1,			   2,		1,			 1
union	  select '[input_data_dump_history]',			    1,				   1,			   2,		1,			 1
--union	  select '[nm_input_data_dump_types]',			    1,				   0,			   2,		0,			 0
/*
union	  select '[sysobjects]',						    1,				   1,			   2,		1,			 0
union	  select '[syscolumns]',						    1,				   1,			   2,		1,			 0
union	  select '[systypes]',						    1,				   0,			   2,		1,			 0
union	  select '[syscomments]',					    1,				   0,			   2,		1,			 0
union	  select '[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]',  1,				   0,			   1,		1,			 0
union	  select '[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]',   1,				   0,			   1,		1,			 0
union	  select '[sys].[schemas]',					    1,				   0,			   1,		1,			 0
union	  select '[sys].[tables]',					    1,				   0,			   1,		1,			 0
union	  select '[sys].[identity_columns]',			    1,				   0,			   1,		1,			 0
*/  

set @db_name = DB_name()

set @new_db_name = @db_name + '_objects'

set @new_db_exists = case when DB_ID(@new_db_name) is not null then 1 else 0 end

if @new_db_exists = 0
    print 'Database ' + @new_db_name + ' not found'

select  @min_id = min(id),
	   @max_id = max(id)
from @tables

set @current_id = isnull(@min_id,0)
set @max_id = isnull(@max_id, -1)

while @current_id <= @max_id
begin

    select  @table_name = table_name,
		  @create_select_view = create_select_view,
		  @create_insert_view = create_insert_view,
		  @points_number = points_number,
		  @select_union_all = select_union_all,
		  @select_union = select_union
    from @tables
    where id = @current_id 

    if @create_select_view = 1
    begin
	   set @select_view_name = '[vw__' + @table_name +  '_select]'

	   set @select_view_name = lower(replace(replace(replace(@select_view_name, '.', '_'),'[',''),']',''))

	   if OBJECT_ID(@select_view_name, 'V') is null
		  set  @sql_view_select = 'create view '
	   else
		  set  @sql_view_select = 'alter view '

	   set @sql_view_select = @sql_view_select + @select_view_name + CHAR(13) + CHAR(10) + ' as ' + CHAR(13) + CHAR(10) 
	   set @sql_view_select = @sql_view_select + 'select * from ' + @table_name

	   if @new_db_exists = 1
	   begin 

		  if @select_union = 1
		  begin
			 set @sql_view_select = @sql_view_select  + CHAR(13) + CHAR(10) + ' union ' + case when @select_union_all = 1 then ' all ' else '' end
		  end
		  
		  set @sql_view_select = @sql_view_select  + CHAR(13) + CHAR(10) + 'select * from [' + @new_db_name + ']' + REPLICATE('.', @points_number) +  @table_name 
	   end
	   
	   --print @sql_view_select

	   BEGIN TRY
		  execute(@sql_view_select)

		  print 'create/alter view ' + @select_view_name + ' OK' 
	   END TRY
	   BEGIN CATCH
		   print 'create/alter view ' + @select_view_name + ' NOTOK'

		   SELECT
				@ErrorMessage = ERROR_MESSAGE(),
				@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
		  SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: ' + @ErrorMessage;
		  RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine)

	   END CATCH

    end 

    if @create_insert_view = 1
    begin
	   set @insert_view_name = '[vw__' + @table_name +  '_insert]'

	   set @insert_view_name = lower(replace(replace(replace(@insert_view_name, '.', '_'),'[',''),']',''))

	   if OBJECT_ID(@insert_view_name, 'V') is null
		  set  @sql_view_insert = 'create view '
	   else
		  set  @sql_view_insert = 'alter view '

	   set @sql_view_insert = @sql_view_insert + @insert_view_name + CHAR(13) + CHAR(10) + ' as ' + CHAR(13) + CHAR(10) 
	   
	   if @new_db_exists = 1 
	   begin
		  set @sql_view_insert = @sql_view_insert + + CHAR(13) + CHAR(10) + 'select * from [' + @new_db_name + ']' + REPLICATE('.', @points_number) +  @table_name
	   end 
	   else
	   begin
		  set @sql_view_insert = @sql_view_insert + 'select * from ' + @table_name
	   end
	    
	   --print @sql_view_insert

	   BEGIN TRY
		  execute(@sql_view_insert)

		  print 'create/alter view ' + @insert_view_name + ' OK' 
	   END TRY
	   BEGIN CATCH
		   print 'create/alter view ' + @insert_view_name + ' NOTOK'

		   SELECT
				@ErrorMessage = ERROR_MESSAGE(),
				@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
		  SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: ' + @ErrorMessage;
		  RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine)

	   END CATCH

    end 




    set @current_id = @current_id + 1
end





GO
