SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_sys_database_objects_create]
    @db_name nvarchar(128)

as

SET nocount on;

DECLARE @dbs TABLE(idx int IDENTITY(1,1), name sysname, data_file_path varchar(512), log_file_path varchar(512))


INSERT INTO @dbs (name, data_file_path, log_file_path)
SELECT  d.name, 
	   (SELECT mf.physical_name FROM master.sys.master_files mf where d.database_id = mf.database_id AND mf.type_desc = 'ROWS'),
	   (SELECT mf.physical_name FROM master.sys.master_files mf where d.database_id = mf.database_id AND mf.type_desc = 'LOG')
FROM   master.sys.databases d
    --inner join master.sys.master_files mf ON d.database_id = mf.database_id
WHERE  CASE
         WHEN d.state_desc != 'OFFLINE' AND d.state_desc = 'ONLINE' THEN OBJECT_ID(QUOTENAME(d.name) + '.[dbo].[ais_options]', 'U')
       END IS NOT NULL
    and (@db_name is null or d.name = @db_name)

--SELECT * FROM @dbs

DECLARE @idx int
DECLARE @dbname sysname
DECLARE @data_file_path varchar(512)
DECLARE @log_file_path varchar(512)
DECLARE @db_base_path varchar(512)
DECLARE @new_db_name sysname
DECLARE @new_table_name sysname
DECLARE @sql nvarchar(max)
declare @sql_temp nvarchar(max)
declare @view_name sysname
declare @new_db_exists bit

DECLARE
	   @ErrorMessage nvarchar(4000),
	   @ErrorNumber int ,
	   @ErrorSeverity int ,
	   @ErrorState int ,
	   @ErrorLine int,
	   @ErrorProcedure nvarchar(200),
	   @srcdatabasecollation sql_variant 

SELECT @idx = (SELECT MIN(idx) FROM @dbs)

WHILE @idx IS NOT NULL
BEGIN

    SELECT  @dbname = d.name,
		  @data_file_path = d.data_file_path,
		  @log_file_path = d.log_file_path
    from @dbs d
    WHERE d.idx = @idx

    SET @new_db_name = @dbname + '_objects'

    set @new_db_exists = case when DB_ID(@new_db_name) IS NULL then 0 else 1 end

    IF @new_db_exists = 0
    BEGIN

	   --set @db_base_path = REPLACE(@data_file_path, REVERSE(SUBSTRING(REVERSE(@data_file_path),0,CHARINDEX('\',REVERSE(@data_file_path)))) ,'')

	   set @db_base_path = SUBSTRING(@data_file_path, 0,  LEN(@data_file_path) - CHARINDEX('\', REVERSE(@data_file_path)) + 2)

	   PRINT 'Création base: ' + @new_db_name + ', data_file_path=' + @db_base_path + @new_db_name + '.mdf' + ', log_file_path=' + @db_base_path + @new_db_name + '_log.ldf'

	   --SELECT @db_base_path, @db_base_path + @new_db_name + '.mdf', @db_base_path + @new_db_name + '_log.ldf'

	   set @srcdatabasecollation = DATABASEPROPERTYEX(@dbname, 'Collation')

	   SET @sql = N'CREATE DATABASE [{DB_NAME}] ON  PRIMARY 
				    ( NAME = N''{MDF_NAME}'', FILENAME = N''{MDF_PATH}'', SIZE = 6144KB , FILEGROWTH = 10%)
				 LOG ON 
				    ( NAME = N''{LOG_NAME}'', FILENAME = N''{LOG_PATH}'', SIZE = 6144KB , FILEGROWTH = 10%)
				 COLLATE {COLLATION}
				    '

	   SET @sql = REPLACE(@sql, '{DB_NAME}', @new_db_name)
	   SET @sql = REPLACE(@sql, '{MDF_NAME}', @new_db_name)
	   SET @sql = REPLACE(@sql, '{MDF_PATH}', @db_base_path + @new_db_name + '.mdf')
	   SET @sql = REPLACE(@sql, '{LOG_NAME}', @new_db_name + '_log')
	   SET @sql = REPLACE(@sql, '{LOG_PATH}', @db_base_path + @new_db_name + '_log.ldf')
	   SET @sql = REPLACE(@sql, '{COLLATION}', convert(nvarchar(128),@srcdatabasecollation))

	   PRINT @sql
	   
	   BEGIN TRY
		  EXECUTE(@sql)

		  set @sql = 'use [' + @new_db_name + ']' + char(13) + char(10) +	  
				    '
					   CREATE TABLE [dbo].[obj_objects](
						   [obj_object_id] [int] NOT NULL,
						   [obj_is_compressed] [bit] NOT NULL,
						   [obj_compression_type] [varchar](10) NULL,
						   [obj_binary] [image] NULL,
					    CONSTRAINT [PK_obj_objects] PRIMARY KEY CLUSTERED 
					   (
						   [obj_object_id] ASC
					   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
					   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

					   
					   SET ANSI_PADDING OFF
					   
					   ALTER TABLE [dbo].[obj_objects] ADD  CONSTRAINT [DF__obj_objects__obj_is_compressed]  DEFAULT ((0)) FOR [obj_is_compressed]
					   
					   ALTER TABLE [dbo].[obj_objects]  WITH CHECK ADD  CONSTRAINT [CK_obj_objects_obj_compression_type] CHECK  ((isnull([obj_compression_type],''zip'')=''gzip'' OR isnull([obj_compression_type],''zip'')=''tar.gz'' OR isnull([obj_compression_type],''zip'')=''gz'' OR isnull([obj_compression_type],''zip'')=''rar'' OR isnull([obj_compression_type],''zip'')=''zlib'' OR isnull([obj_compression_type],''zip'')=''7zip'' OR isnull([obj_compression_type],''zip'')=''zip''))

					   ALTER TABLE [dbo].[obj_objects] CHECK CONSTRAINT [CK_obj_objects_obj_compression_type]
				    '

		  EXECUTE(@sql)

		  set @new_db_exists = 1

		  print 'create database ' + @new_db_name + ' et table obj_objects OK' 

	   END TRY
	   BEGIN CATCH
		   print 'create database ' + @new_db_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

    --création view

    set @view_name = 'vw__obj_objects_insert'

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

    set @sql = @sql + @view_name + CHAR(13) + CHAR(10) + ' as ' 

    set @sql = @sql + CHAR(13) + CHAR(10) + 'select * from ' + case when @new_db_exists = 1 then '[' + @new_db_name + '].' else '' end  + '[dbo].[obj_objects]'

    BEGIN TRY
	   EXECUTE(@sql)

	   set @view_name = 'vw__obj_objects_select'

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

	   set @sql = @sql + @view_name + CHAR(13) + CHAR(10) + ' as ' + CHAR(13) + CHAR(10) + 'select * from [dbo].[obj_objects]'

	   if @new_db_exists = 1
	   begin
		  set @sql = @sql + CHAR(13) + CHAR(10) + ' union all '
		  set @sql = @sql + CHAR(13) + CHAR(10) + 'select * from [' + @new_db_name + '].[dbo].[obj_objects]'
	   end 
	   
	   EXECUTE(@sql)

	   print 'create views vw__obj_objects_insert and vw__obj_objects_select OK' 

    END TRY
    BEGIN CATCH
		  print 'create views vw__obj_objects_insert and vw__obj_objects_select 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

     
    SELECT @idx = (SELECT MIN(idx) FROM @dbs WHERE idx > @idx)
END






GO
