𝓟𝓵𝓮𝓪𝓼𝓮 add support for Partition Functions and User-Defined Table Types (UDTT) to the global Collation update process performed by the "-Q" switch of SQLSERVR.EXE. As far as I can tell, these are the only two objects that are not modified by this operation.

☑ Yes, I am fully aware that the global Collation update process initiated by the "-Q" switch is undocumented, and hence unsupported. (i.e. MS is not committing to maintaining this particular code)

☒ No, I am 𝑛𝑜𝑡 requesting, even implicitly, that the "-Q" switch become officially documented and supported.

☒ No, I am not expecting 𝑎𝑛𝑦 guarantees / warranties, express or implied, regarding the proper operation of the "-Q" switch, either for what it currently does, or for the additional functionality being requested here. (i.e. MS is not liable for the proper functioning of this particular operation)

😎 👍 YES, it is absolutely 100.0003% ( 🢤 precision issue with FLOAT / Double 😿 ) worthwhile / valuable having these two objects added to a process that will remain unsupported. In fact, it would be a 𝑯𝑼𝑮𝑬 help to a lot of your customers. Changing the Collation of an existing system (i.e. has data and objects containing string columns and/or variables) can be quite painful and error prone. The recommended method of exporting data, scripting out the schema, rebuilding, and then importing that data is both time-consuming and error-prone. This "SQLSERVR -Q" method, while undocumented/unsupported, is rather quick, and in my estimation (based on quite a bit of testing, but unable to see the code, of course) much less error-prone (especially if no code pages change or only non-impacting code page changes).

This "SQLSERVR -Q" method is a blessing for many people, and it would be super appreciated if it could be tweaked just a little to include these two missing objects. Columns in user tables are already being updated, and columns in user-defined table types are stored in the same system table, they are likely just being filtered out (or not included) because most likely the operation can't simply update all columns as some are for system views, and some of those collations shouldn't change (or, so I reckon).

Currently, because these two objects are not included in the "-Q" operation, fixing either of them is a bit painful, especially if there are a lot of them and/or they are used in many places (e.g. "Change collation on partition column (SQL Server 2017)" ( https://dba.stackexchange.com/q/252208/30859 )):

😭 UDTTs need to be dropped and recreated since there is no "ALTER TYPE" statement. However, if any UDTTs are used as input parameters to stored procedures or functions (i.e. TVPs), then you can't drop the UDTT unless you first drop all of its dependent objects. Then you need to recreate those dropped, depenedent objects after recreating the UDDT(s).

😭 Partition Functions are even worse. You can't alter them, and you can't drop them if they are used in a partition scheme, and you can't drop the partition scheme if it is used by an index, PK or unique constraint, or a heap. On top of that, you need to handle non-clustered indexes, clustered indexes + PKs and unique constraints, and heaps differently.

I have the steps (and some queries) to fix these objects documented in the "Finding and Fixing Ignored Objects" section of "Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?" ( https://sqlquantumleap.com/2018/06/11/changing-the-collation-of-the-instance-and-all-columns-across-all-user-databases-what-could-possibly-go-wrong/#findandfixignoredobjects )

-- Find string input parameters of partition functions:
SELECT *
FROM sys.partitionparameters prm
WHERE prm.[collationname] IS NOT NULL;

EXEC sphelptext N'sys.partitionparameters';

-- system table: sys.systypedsubobjs
-- column: collationid

-- Find all string columns of UDTTs:
SELECT *
FROM sys.objects tab
INNER JOIN sys.columns col

    ON col.[object_id] = tab.[object_id]

WHERE tab.[type] = N'TT'
AND col.[collation_name] IS NOT NULL;

EXEC sp_helptext N'sys.columns';

-- system table: sys.syscolpars
-- column: collationid

　😸　🗦𝕋ℍ𝔸ℕ𝕂𝕊🗧　🙌

Sql Quantum Leap ( https://SqlQuantumLeap.com/ )