Homologar la intercalación de las columnas de todas las tablas para una base de datos

Hace tiempo inicie un espacio de blog que perdí, sin embargo hice algunas entradas que he rescatado y quiero aquí mantener, por ello aquí les dejo una entrada que creo puede ser de utilidad.


Muchas veces cuando estamos desarrollando o bien administrando un servidor de base de datos se presenta la necesidad de adquirir una base de datos que fue generada en un servidor diferente, ya sea por la vía de una restauración o por agregar los archivo correspondientes en el directorio seleccionado, es común que esta base de datos adquirida se presenta con una intercalación diferente a la que se tiene definida en el servidor. Para obtener la intercalación que se tiene en el servidor, es la que está definida en la base de datos master, de tal forma que es posible obtener esta información utilizando la consulta siguiente:

SELECT collation_name FROM sys.databases where name = 'master'


Como todo mundo sabe es importante que la intercalación de las bases de datos sea la misma que la que se tiene en el servidor para mejorar el rendimiento, ya que en caso contrario será necesario que en las consultas se haga referencia a la intercalación adecuada. Si bien es cierto que es muy sencillo realizarlo con las sentencias de SQL Server, de tal forma que para cambiar la intercalación utilizamos la siguiente sentencia:

ALTER DATABASE [DatabaseName] COLLATE [CollateName]


Si bien esta sentencia cambia la intercalación de la base de datos, no necesariamente cambia la intercalación de todas las columnas de las tablas de la base de datos. Entonces qué hacer para ese caso, una solución es abrir la definición de cada tabla y cambiar manualmente la intercalación, o utilizar la sentencia ALTER TABLE para cambiar la intercalación de cada columna, esto puede ser una tarea muy lenta.

Cuando ocurre esta situación hace falta una herramienta o bien un script que nos ayude a cambiar la intercalación de todas las columnas de tipo char, varchar, nchar, nvarchar, ntext o text que haya en las distintas tablas de la base de datos. Es entonces útil saber que toda la información de la base de datos se encuentra definida en las tablas del sistema, mas específicamente la tabla INFORMATION_SCHEMA.COLUMNS contiene todas las columnas de las tablas, por lo que la siguiente consulta nos arrojara la información de todas las columnas de tipo char, varchar, nchar, nvarchar y text de las tablas de la base de datos

USE DatabaseName

SELECT Table_Schema+'.'+Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')

  AND (Data_Type = 'char' OR Data_Type = 'varchar' OR Data_Type = 'nchar' OR Data_Type = 'nvarchar' OR Data_Type = 'text' OR Data_Type = 'ntext')

ORDER BY Table_Schema,Table_Name

Esto nos dará como resultado las tablas, con sus respectivos esquemas, en nombre de la columna, el tipo y la longitud de la columna, existe una consideración para los tipos de datos varchar(max) y nvarchar(max) que a partir de la versión 2005 se incluye para sustituir los tipos text, que el valor para la longitud aparece como -1.

En una base de datos con 50 tablas en la que participe, se localizaron aproximadamente 210 columnas que contenían los tipos de datos especificados anteriormente. Imagínense que se hiciera la siguiente instrucción por cada una de las columnas listadas.

ALTER TABLE TableName ALTER COLUMN ColumnName DataType(MaxLength) COLLATE CollateName

Donde; DataType es uno de los tipos char, varchar, nchar, nvarchar, ntext o text
MaxLength es la longitud de la columna, no se especifica en el caso de text o ntext.

Es por ello que hice un script que utiliza un cursor para llevar a cabo el cambio en las columnas de todas las tablas por la intercalación especificada, como se muestra a continuación

/*********************************************************************************************

** Autor:    Julio J. Bueyes (jjbueyes@hotmail.com)

** Fecha:    Abril, 2009

** Proposito: Homologar la intercalacion de una base de datos y las columnas de las tablas

**                         con la intercalacion definida en el servidor

**********************************************************************************************/

-- Definicion de variables para el proceso

DECLARE @TableName AS varchar(100)      --<< Almacena el nombre de la tabla junto con el esquema

DECLARE @ColumnName AS varchar(60)      --<< Almacena el nombre de la columna

DECLARE @DataType AS varchar(10)  --<< Almacena el tipo de dato de la columna

DECLARE @MaxLength as varchar(5)  --<< Almacena la longitud de la columna

DECLARE @Command as varchar(1000) --<< Contiene el comando sql a ejecutar

-- Definicion de variables que seran usadas como constantes

DECLARE @Collate as varchar(50)         --<< Contendra el valor de la intercalacion

DECLARE @Database as varchar(50)  --<< Contendra el nombre de la base de datos

-- Establecer el valor de las constantes

SELECT @Collate = collation_name FROM sys.databases where name = 'master' --<< Establece la intercalacion que se utiliza en el servidor

SET @Database = 'DatabaseName'          --<< Establecer el nombre de la base de datos que se desea homologar

-- Cambiar la intercalacion de la base de datos especificada por la del servidor

SET @Command = 'ALTER DATABASE ' + @Database + ' COLLATE ' + @Collate

EXEC (@Command)

-- Establecer el ambito de la base de datos

SET @Command = 'USE ' + @Database

EXEC (@Command)

--Declarar un cursor con la informacion de las columnas de todas las tablas de la base de datos

DECLARE CurTables CURSOR

FOR

SELECT Table_Schema+'.'+Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')

  AND (Data_Type = 'char' OR Data_Type = 'varchar' OR Data_Type = 'nchar' OR Data_Type = 'nvarchar' OR Data_Type = 'text' OR Data_Type = 'ntext')

ORDER BY Table_Schema,Table_Name

-- Abrir el cursor y obtener el primer registro

OPEN CurTables

FETCH NEXT FROM CurTables INTO @TableName, @ColumnName, @DataType, @MaxLength

-- Mientras existan registros en el cursor se cambiara la intercalacion de las columnas en la tablas de la base de datos

WHILE @@FETCH_STATUS = 0

BEGIN

       -- Identifica si es text o de otro tipo de dato

       IF @DataType = 'char' OR @DataType = 'varchar' OR @DataType = 'nchar' OR @DataType = 'nvarchar'

             SET @Command = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @DataType + '(' + CASE @MaxLength WHEN -1 THEN 'max' ELSE @MaxLength END + ') COLLATE ' + @Collate

       ELSE

             SET @Command = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @DataType + ' COLLATE ' + @Collate

       -- se mantiene un registro de las instrucciones ejecutadas

       PRINT @Command

       -- ejecuta el comando generado

       EXEC (@Command)

       -- obtiene el nuevo registro del cursor

       FETCH NEXT FROM CurTables INTO @TableName, @ColumnName, @DataType, @MaxLength

END

-- Cerrar y liberar el cursor

CLOSE CurTables

DEALLOCATE CurTables

      

--- Las columnas de las tablas de la base de datos especificada se han homologado con la intercalacion del servidor

Como pueden observar se obtiene la intercalación del servidor y se aplica a la base de datos y a las columnas de tipo char, nchar, varchar, nvarchar, text y ntext, con el objetivo de mejorar el desempeño de las consultas y evitar los usos de la especificación de la intercalación en las mismas.

Espero que esto les ayude a mejorar su trabajo, si tienen algún comentario o sugerencia no duden en enviármela al correo especificado. Gracias.