SELECT tA.FieldName As [Field Name],
       COALESCE(tO_A.[desc], tO_B.[desc], tO_C.Name, tA.OldVAlue) AS [Old Value],
       COALESCE(tN_A.[desc], tN_B.[desc], tN_C.Name, tA.NewValue) AS [New Value],
       U.UserName AS [User Name],
       CONVERT(varchar, tA.ChangeDate) AS [Change Date]
  FROM D tA
       JOIN
       [DRTS].[dbo].[User] U
         ON tA.UserID = U.UserID
       LEFT JOIN
       A tO_A
         on tA.FieldName = 'AID'
        AND tA.oldValue = CONVERT(VARCHAR, tO_A.ID)
       LEFT JOIN
       A tN_A
         on tA.FieldName = 'AID'
        AND tA.newValue = CONVERT(VARCHAR, tN_A.ID)
       LEFT JOIN
       B tO_B
         on tA.FieldName = 'BID'
        AND tA.oldValue = CONVERT(VARCHAR, tO_B.ID)
       LEFT JOIN
       B tN_B
         on tA.FieldName = 'BID'
        AND tA.newValue = CONVERT(VARCHAR, tN_B.ID)
       LEFT JOIN
       C tO_C
         on tA.FieldName = 'CID'
        AND tA.oldValue = tO_C.Name
       LEFT JOIN
       C tN_C
         on tA.FieldName = 'CID'
        AND tA.newValue = tN_C.Name
 WHERE U.Fullname = @SearchTerm
ORDER BY tA.ChangeDate

SELECT
    col.name, col.collation_name
FROM
    sys.columns col
WHERE
    object_id = OBJECT_ID('YourTableName')

ALTER TABLE YourTableName
  ALTER COLUMN OffendingColumn
    VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL

SELECT
    fti.object_Id,
    OBJECT_NAME(fti.object_id) 'Fulltext index',
    fti.is_enabled,
    i.name 'Index name',
    OBJECT_NAME(i.object_id) 'Table name'
FROM
    sys.fulltext_indexes fti
INNER JOIN
    sys.indexes i ON fti.unique_index_id = i.index_id

DROP FULLTEXT INDEX ON (tablename)

...WHERE
    fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT

LEFT JOIN C tO_C on tA.FieldName = 'CID' AND tA.oldValue COLLATE Latin1_General_CI_AS = tO_C.Name

...
and table1.Code = table2.Code
...

...
and table1.Code COLLATE DATABASE_DEFAULT = table2.Code COLLATE DATABASE_DEFAULT
...

select
   *
from sd
inner join pd on sd.SCaseflowID collate  Latin1_General_CS_AS = pd.PDebt_code collate  Latin1_General_CS_AS

-- Create Case Sensitive Database
CREATE DATABASE CaseSensitiveDatabase
COLLATE SQL_Latin1_General_CP1_CS_AS -- or any collation you require
GO
USE CaseSensitiveDatabase
GO
SELECT *
FROM sys.types
GO
--rest of your script here

-- Create Case In-Sensitive Database
CREATE DATABASE CaseInSensitiveDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS -- or any collation you require
GO
USE CaseInSensitiveDatabase
GO
SELECT *
FROM sys.types
GO
--rest of your script here

DECLARE @tableName VARCHAR(MAX)
SET @tableName = 'affiliate'
--EXEC sp_columns @tableName
SELECT  'Alter table ' + @tableName + ' alter column ' + col.name
        + CASE ( col.user_type_id )
            WHEN 231
            THEN ' nvarchar(' + CAST(col.max_length / 2 AS VARCHAR) + ') '
          END + 'collate Latin1_General_CI_AS ' + CASE ( col.is_nullable )
                                                    WHEN 0 THEN ' not null'
                                                    WHEN 1 THEN ' null'
                                                  END
FROM    sys.columns col
WHERE   object_id = OBJECT_ID(@tableName)

CREATE PROCEDURE [dbo].[sz_pipeline001_collation]
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name +
    CASE systypes.NAME
    WHEN 'text' THEN ' '
    ELSE
    '(' + RTRIM(CASE SYSCOLUMNS.length
    WHEN -1 THEN 'MAX'
    ELSE CONVERT(CHAR,SYSCOLUMNS.length)
    END) + ') '
    END

    + ' ' + ' COLLATE Latin1_General_CI_AS ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
    FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
    WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
    AND SYSOBJECTS.TYPE = 'U'
    AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
    AND SYSCOLUMNS.COLLATION IS NOT NULL
    AND NOT ( sysobjects.NAME LIKE 'sys%' )
    AND NOT ( SYSTYPES.name LIKE 'sys%' )

END

...LEFT Outer join ImportDB..C4CTP C4 on C4.C4CTP COLLATE Latin1_General_CS_AS=CUS_Type COLLATE Latin1_General_CS_AS

----------
AND db1.tbl1.fiel1 COLLATE DATABASE_DEFAULT =db2.tbl2.field2 COLLATE DATABASE_DEFAULT