
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_sys_md_create_ck_string_pk]
	@create bit = 1,
	@execute bit = 1
AS

	/*
	[DGE]
	Procedura genereaza pentru toate campurile de tip sir de caractere
	o constrangere CK__<tabela>_<camp>__string_pk.
	
	Aceasta constrangere verifica urmatoarele:
	- campul nu contine sirul vid sau un sir de spatii;
	- campul nu contine '*'
	- campul nu contine '%'
	*/



	/* lista contrangerilor */
	SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
		   PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
		   PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
		   PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
		   FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
		   FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
		   FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
		   FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
		   -- Force the column to be non-nullable (see SQL BU 325751)
		   --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
		   UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') 
											WHEN 1 THEN 0
											ELSE 1
										  END),
		   DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') 
											WHEN 1 THEN 0
											ELSE 1
										  END),
		   FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
		   PK_NAME = CONVERT(SYSNAME,I.NAME),
		   DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE
	INTO	#tmpFK	
	FROM   SYS.ALL_OBJECTS O1,
		   SYS.ALL_OBJECTS O2,
		   SYS.ALL_COLUMNS C1,
		   SYS.ALL_COLUMNS C2,
		   SYS.FOREIGN_KEYS F
		   INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
			 ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
		   INNER JOIN SYS.INDEXES I
			 ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
				 AND F.KEY_INDEX_ID = I.INDEX_ID)
	WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
		   AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
		   AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
		   AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
		   AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
		   AND C2.COLUMN_ID = K.PARENT_COLUMN_ID	
		   
		   
		   

	DECLARE	@table ais_name,
			@field ais_name,
			@ck sysname,
			@sql VARCHAR(4000)
			
	DECLARE c_fields INSENSITIVE CURSOR 
	FOR
		SELECT  fld_table_name, fld_field_name
		FROM    md_fields
		where	fld_pk != 0 
		and		(
				fld_type in ('varchar', 'ais_name')
				or
				fld_type like 'ais_code%'
				)
		and		not exists
				(
				SELECT	1
				FROM	#tmpFK
				WHERE	FKTABLE_NAME = fld_table_name
				AND		FKCOLUMN_NAME = fld_field_name
				)

		/*
		DGE 27/02/2014
		J'ajoute cette condition pour exclure les champs comme par exemple "seqc_prefix", qui est un champ qui fait partie de la clé
		primaire, mais qui doit stocker des "".
		*/
		and		(
				fld_field_name like '%name'
				or		
				fld_field_name like '%code'
				)

	open c_fields
	
	fetch next from c_fields into @table, @field
	
	while (@@FETCH_STATUS = 0)
	begin
		
		set @ck = 'CK__' + @table + '_' + @field + '__string_pk'

				


		if @create != 0 
		BEGIN
			IF NOT EXISTS ( SELECT	1
							FROM	sysobjects
							where	name = @ck)
			BEGIN
				set @sql = 'ALTER TABLE ' + @table + 
						' ADD CONSTRAINT ' + @ck +  ' CHECK (len(ltrim(rtrim(%f%))) > 0 and charindex(''*'', %f%) = 0 and charindex(''%'', %f%) = 0)'
				select @sql = REPLACE(@sql, '%f%', @field)	
				
				if @execute != 0 
					exec (@sql)
				else
					print (@sql)
			END
			
		END
		
		ELSE
		
		BEGIN

			IF EXISTS ( SELECT	1
							FROM	sysobjects
							where	name = @ck)
			BEGIN
				set @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @ck

				if @execute != 0 
					exec (@sql)
				else
					print (@sql)
			END
		
		END		
	
	
		fetch next from c_fields into @table, @field
	end
	
	close c_fields
	deallocate c_fields



GO
