Thinking outside the box

# re: Change collation

Hi Peso,

I may need to do exactly this in the future so this looks like something I'd be interested in.

I've taken your beta and ran a few test cases. I'm running into problems dropping (and probably recreating FK where there are multiple columns referenced)

For example this Key breaks your script as it tries to drop it twice.

USE [CollationChange_XXX]
GO
ALTER TABLE [dbo].[TabGroupItems] WITH CHECK ADD CONSTRAINT [FK_TabGroupItems_TabGroup] FOREIGN KEY([companyId], [tabGroupName])
REFERENCES [dbo].[TabGroup] ([companyId], [name])
GO
ALTER TABLE [dbo].[TabGroupItems] CHECK CONSTRAINT [FK_TabGroupItems_TabGroup]

This is turned into two DROP lines by this select statement which is lifted from your CTE:


SELECT
o.[schema_Id] AS [schema_Id]
, QUOTENAME(o.[name]) AS [CONSTRAINT_NAME]
, QUOTENAME(SCHEMA_NAME(po.[schema_id])) AS [FOREIGN_TABLE_SCHEMA]
, QUOTENAME(po.[name]) AS [FOREIGN_TABLE_NAME]
, QUOTENAME(rccu.[COLUMN_NAME]) AS [FOREIGN_COLUMN_NAME]
, QUOTENAME(SCHEMA_NAME(ro.[schema_id])) AS [PRIMARY_TABLE_SCHEMA]
, QUOTENAME(ro.[name]) AS [PRIMARY_TABLE_NAME]
, QUOTENAME(rc.[name]) AS [PRIMARY_COLUMN_NAME]
, CASE fk.[is_disabled]
WHEN 0 THEN 'CHECK'
ELSE 'NOCHECK'
END AS [Enabled]
FROM
sys.foreign_keys AS fk
JOIN sys.objects AS o ON o.[object_id] = fk.[object_id]
JOIN sys.objects AS po ON po.[object_id] = fk.[parent_object_id]
JOIN sys.objects AS ro ON ro.[object_id] = fk.[referenced_object_id]
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS rccu ON
rccu.[CONSTRAINT_SCHEMA] = SCHEMA_NAME(o.[schema_id])
AND rccu.[CONSTRAINT_NAME] = o.[name]
AND rccu.[TABLE_SCHEMA] = SCHEMA_NAME(po.[schema_id])
AND rccu.[TABLE_NAME] = po.[name]

JOIN sys.index_columns AS ric ON
ric.[object_id] = fk.[referenced_object_id]
AND ric.[index_id] = fk.[key_index_id]
AND ric.[is_included_column] = 0

JOIN sys.columns AS rc ON
rc.[object_id] = fk.[referenced_object_id]
AND rc.[column_id] = ric.[column_id]
WHERE
fk.[is_ms_shipped] = 0
-- AND rc.[collation_name] IS NOT NULL
AND QUOTENAME(po.[name]) = '[TabGroupItems]'


I'm guessing that to fix the problem we want to concatenate the rows for foreign columns into 1 comma separated list of those columns?

I'll be happy to help with testing.

Charlie.

# re: Change collation

Yes, there are some issues left to fix. One of them is composite keys as you describe above.
Another is the index thing.

Thank you for your offer. I will continue to update this blog post when the script is updated.

# re: Change collation

I think I have the combined keys working.... (not extensively tested). Performance is... bad but that's going to be a secondary concern for this..


;WITH base AS (
SELECT
o.[schema_Id] AS [schema_Id]
, QUOTENAME(o.[name]) AS [CONSTRAINT_NAME]
, QUOTENAME(SCHEMA_NAME(po.[schema_id])) AS [FOREIGN_TABLE_SCHEMA]
, QUOTENAME(po.[name]) AS [FOREIGN_TABLE_NAME]
, QUOTENAME(rccu.[COLUMN_NAME]) AS [FOREIGN_COLUMN_NAME]
, QUOTENAME(SCHEMA_NAME(ro.[schema_id])) AS [PRIMARY_TABLE_SCHEMA]
, QUOTENAME(ro.[name]) AS [PRIMARY_TABLE_NAME]
, QUOTENAME(rc.[name]) AS [PRIMARY_COLUMN_NAME]
, CASE fk.[is_disabled]
WHEN 0 THEN 'CHECK'
ELSE 'NOCHECK'
END AS [Enabled]
FROM
sys.foreign_keys AS fk
JOIN sys.objects AS o ON o.[object_id] = fk.[object_id]
JOIN sys.objects AS po ON po.[object_id] = fk.[parent_object_id]
JOIN sys.objects AS ro ON ro.[object_id] = fk.[referenced_object_id]

JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS rccu ON
rccu.[CONSTRAINT_SCHEMA] = SCHEMA_NAME(o.[schema_id])
AND rccu.[CONSTRAINT_NAME] = o.[name]
AND rccu.[TABLE_SCHEMA] = SCHEMA_NAME(po.[schema_id])
AND rccu.[TABLE_NAME] = po.[name]

JOIN sys.index_columns AS ric ON
ric.[object_id] = fk.[referenced_object_id]
AND ric.[index_id] = fk.[key_index_id]
AND ric.[is_included_column] = 0

JOIN sys.columns AS rc ON
rc.[object_id] = fk.[referenced_object_id]
AND rc.[column_id] = ric.[column_id]
WHERE
fk.[is_ms_shipped] = 0
-- AND rc.[collation_name] IS NOT NULL
-- AND QUOTENAME(po.[name]) = '[TabGroupItems]'
)
SELECT
ROW_NUMBER() OVER (ORDER BY [schema_id]) AS [RowID]
, [CONSTRAINT_NAME]
, [FOREIGN_TABLE_SCHEMA]
, [FOREIGN_TABLE_NAME]
, LEFT([FOREIGN_COLUMN_NAMES], LEN([FOREIGN_COLUMN_NAMES]) -1) AS [FOREIGN_COLUMN_NAMES]
, [PRIMARY_TABLE_SCHEMA]
, [PRIMARY_TABLE_NAME]
, LEFT([PRIMARY_COLUMN_NAMES], LEN([PRIMARY_COLUMN_NAMES]) -1) AS [PRIMARY_COLUMN_NAMES]
, [Enabled]
FROM
(
SELECT DISTINCT
b.[schema_Id]
, b.[CONSTRAINT_NAME]
, b.[FOREIGN_TABLE_SCHEMA]
, b.[FOREIGN_TABLE_NAME]
, b.[PRIMARY_TABLE_SCHEMA]
, b.[PRIMARY_TABLE_NAME]
, b.[Enabled]

, ( SELECT DISTINCT [FOREIGN_COLUMN_NAME] + ', '
FROM base fc
WHERE
b.[schema_Id] = fc.[schema_Id]
AND b.[CONSTRAINT_NAME] = fc.[CONSTRAINT_NAME]
AND b.[FOREIGN_TABLE_SCHEMA] = fc.[FOREIGN_TABLE_SCHEMA]
AND b.[FOREIGN_TABLE_NAME] = fc.[FOREIGN_TABLE_NAME]
AND b.[PRIMARY_TABLE_SCHEMA] = fc.[PRIMARY_TABLE_SCHEMA]
AND b.[PRIMARY_TABLE_NAME] = fc.[PRIMARY_TABLE_NAME]
AND b.[Enabled] = fc.[Enabled]
FOR XML PATH ('')
)
AS [FOREIGN_COLUMN_NAMES]

, ( SELECT DISTINCT [PRIMARY_COLUMN_NAME] + ', '
FROM base pcn
WHERE
b.[schema_Id] = pcn.[schema_Id]
AND b.[CONSTRAINT_NAME] = pcn.[CONSTRAINT_NAME]
AND b.[FOREIGN_TABLE_SCHEMA] = pcn.[FOREIGN_TABLE_SCHEMA]
AND b.[FOREIGN_TABLE_NAME] = pcn.[FOREIGN_TABLE_NAME]
AND b.[PRIMARY_TABLE_SCHEMA] = pcn.[PRIMARY_TABLE_SCHEMA]
AND b.[PRIMARY_TABLE_NAME] = pcn.[PRIMARY_TABLE_NAME]
AND b.[Enabled] = pcn.[Enabled]
FOR XML PATH ('')
)
AS [PRIMARY_COLUMN_NAMES]
FROM
base b
)
comb

Charlie