How to update a particular columns in all tables list RRS feed

  • Question

  • Dear all,

    I manage to get all table where collation is different from SQL server database.
    Now from that list of return tables, I need to circle through that list and then update the collation column to be the same as database .

    This is the querry I use to get the list of tables :

    USE mydb
    DECLARE @DB_Collation VARCHAR(50)
    SET @DB_Collation=CAST(DATABASEPROPERTYEX('mydbce', 'Collation') as varchar(50))
    SELECT as 'Schema_Name', as Table_Name, AS Column_Name,
    c.collation_name AS Collation,
    @DB_Collation AS Database_Collation
    FROM sys.schemas s
    INNER JOIN sys.tables t
    ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c
    ON c.object_id = t.object_id
    WHERE collation_name is not null
    AND collation_name <> @DB_Collation
    ORDER BY Column_Name

    What is the most efficient way to do this ?



    • Edited by wakefun Friday, September 6, 2019 1:11 PM
    Friday, September 6, 2019 1:07 PM