Drop and re-create DateFrom/DateTo constraints (WHERE SoftDelete-Status = 1) if table exists

DropCreate_DateFromToConstraint.sql

SELECT  
	 --table_name 
	--,column_name, 
	--,' SELECT ''' + REPLACE(table_name, '''', '''''') + ''' AS tableName, * FROM ' + QUOTENAME(table_name) + ' WHERE ' + QUOTENAME(column_name) + ' > ' + REPLACE(QUOTENAME(column_name), 'Von', 'Bis') + '; ' AS cmd 
	
	--,
'


IF EXISTS(
	SELECT itc.* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS itc 
	WHERE TABLE_SCHEMA = ''' + REPLACE(TABLE_SCHEMA, '''', '''''') + ''' 
	AND TABLE_NAME = ''' + REPLACE(TABLE_NAME, '''', '''''') + ''' 
	AND CONSTRAINT_NAME = ''' + 'CHK_' + table_name + '_' + column_name + '_LESSOREQUAL_' + REPLACE(column_name, 'Von', 'Bis') + ''' 
)
ALTER TABLE "' + TABLE_SCHEMA + '"."' + table_name + '" DROP CONSTRAINT "CHK_' + table_name + '_' + column_name + '_LESSOREQUAL_' + REPLACE(column_name, 'Von', 'Bis') + '"  

GO

IF EXISTS(
	SELECT ischema.* FROM INFORMATION_SCHEMA.tables AS ischema 
	WHERE ischema.TABLE_NAME = ''' + REPLACE(TABLE_NAME, '''', '''''') + ''' 
	AND ischema.TABLE_SCHEMA = ''' + REPLACE(TABLE_SCHEMA, '''', '''''') + ''' 
	AND ischema.TABLE_TYPE = ''BASE TABLE'' 
) 
ALTER TABLE "' + TABLE_SCHEMA + '"."' + table_name + '"  WITH NOCHECK 
	ADD CONSTRAINT "CHK_' + table_name + '_' + column_name + '_LESSOREQUAL_' + REPLACE(column_name, 'Von', 'Bis') + '" CHECK ((' + column_name + ' <= ' + REPLACE(column_name, 'Von', 'Bis') + ' OR ' + REPLACE(column_name, 'DatumVon', 'Status') + ' <> (1) ) ) 

GO

IF EXISTS(
	SELECT ischema.* FROM INFORMATION_SCHEMA.tables AS ischema 
	WHERE ischema.TABLE_NAME = ''' + REPLACE(TABLE_NAME, '''', '''''') + ''' 
	AND ischema.TABLE_SCHEMA = ''' + REPLACE(TABLE_NAME, '''', '''''') + ''' 
	AND ischema.TABLE_TYPE = ''BASE TABLE'' 
) 
ALTER TABLE "' + TABLE_SCHEMA + '"."' + table_name + '" CHECK CONSTRAINT "CHK_' + table_name + '_' + column_name + '_LESSOREQUAL_' + REPLACE(column_name, 'Von', 'Bis') + '" 

GO

' AS cmd 

FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME LIKE 'T\_%' ESCAPE '\' 
AND TABLE_NAME NOT LIKE 'T\_Import\_%' ESCAPE '\' 

AND 
( 
	COLUMN_NAME LIKE '%DatumVon' 
	--OR COLUMN_NAME LIKE '%DatumBis' 
) 

--AND table_name = 'T_AP_Anlage' 

ORDER BY TABLE_NAME