Category Archives: SQL

Enable or Disable Check Constraints on SQL Server

I found a very handy SQL script on this post, that allows you to enable or disable all constraints on a database. The only problem I had was that my database uses schemas to organize the tables, so the script failed to work.

I made a modification on the script to solve the problem, so here it is:

/************************** DISABLE ALL TABLE CONSTRAINTS **********************************
This script will enable or disable all constraints on all tables within the database
that it is run in.
********************************************************************************************/
 
SET NOCOUNT ON
SET ROWCOUNT 0 
 
DECLARE @COUNT INT
DECLARE @String nvarchar (1000)
DECLARE @ConstraintName VARCHAR(128)
DECLARE @TableName VARCHAR(128)
DECLARE @SchemaName VARCHAR(128)
 
--Find all constraints and their respective tables from the sysobjects table and place into a temp table.
--Primary Key and Unique Constraints via Unique Indexes are not disabled through this command
--You should use the ALTER INDEX...DISABLE command in SQL Server 2005
SELECT
	o.name AS ConstraintName,
	sc.name AS SchemaName,
	object_name(o.parent_obj) AS TableName
INTO #Const_Table
FROM sysobjects o, sys.schemas sc
WHERE xtype IN ('F') AND o.uid = sc.schema_id
 
SELECT @COUNT = COUNT(*) FROM #Const_Table
 
--Setting the rowcount to one allows for one row from the temp table to be picked off at a time.
--Used as an alternative to a cursor.
SET ROWCOUNT 1
 
--Loop until all rows in temp table have been processed.
WHILE @COUNT > 0
BEGIN
	--The rowcount of one ensures that only one tablename and constraint name is picked.
	SELECT @TableName = TableName, @SchemaName = SchemaName, @ConstraintName = ConstraintName
	FROM #Const_Table
 
	--Comment or uncomment the following statements according to your needs
	--Build execution string to enable constraint.
	SET @String = 'ALTER TABLE ['+ @SchemaName + '].['+ @TableName + '] WITH CHECK CHECK CONSTRAINT [' + @ConstraintName +']'
	--Build execution string to disable constraint.
	--SET @String = 'ALTER TABLE ['+ @SchemaName + '].['+ @TableName + '] NOCHECK CONSTRAINT [' + @ConstraintName +']'
 
	--Execute the SQL
	EXEC sp_executesql @string
 
	--Remove this row from the temp table, since it has now been processed.
	DELETE FROM #Const_Table
	WHERE ConstraintName = @ConstraintName AND TableName = @TableName AND SchemaName = @SchemaName
 
	SET @COUNT = @COUNT - 1
END -- Loop
 
DROP TABLE #Const_Table
 
SET ROWCOUNT 0

Count the total number of records on a MySQL database or per table

To count the total number of records on a MySQL database, we could run the following command:

SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{tablename}';

If we need to know the number of records per each table, we can run:

SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{tablename}';

Source: Stack Overflow

Duplicate a MySQL database on a single line

On this article, it is shown a very convenient way to duplicate a complete MySQL database using a single line of code:

$ mysqladmin create {dest_db_name} -u {username} --password={password} && \
mysqldump -u {username} --password={password} {source_db_name} | \
mysql -u {username} --password={password} {dest_db_name}

Where:

  • {dest_db_name}: Name of the destination database.
  • {source_db_name}: Name of the database to be duplicated.
  • {username}: MySQL username.
  • {password}: MySQL password.

If we need to duplicate it to another server, we would do something like this:

$ mysqladmin create {dest_db_name} -u {dest_username} --password={dest_password} -h {dest_server} && \
mysqldump -u {source_username} --password={source_password} -h {source_server} {source_db_name} | \
mysql -u {dest_username} --password={dest_password} -h {dest_server} {dest_db_name}

Where:

  • {source_db_name}: Name of the source database.
  • {source_server}: Source MySQL server.
  • {source_username}: Source MySQL username.
  • {source_password}: Source MySQL password.
  • {dest_db_name}: Name of the destination database.
  • {dest_server}: Destination MySQL server.
  • {dest_username}: Destination MySQL username.
  • {dest_password}: Destination MySQL password.

Determinar la versión exacta del SQL Server

Para determinar cuál es la versión exacta de un servidor SQL Server (ya sea 2000, 2005 ó 2008), basta con ejecutar la siguiente consulta:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition')

En mi caso, la consulta me devuelve algo como esto:

9.00.3080.00 SP2 Developer Edition

Para versiones anteriores de SQL Server, podemos utilizar esta otra consulta:

SELECT @@VERSION

Vía: Microsoft Support

Quitar una cadena de texto de muchos registros usando SQL

En una aplicación en la que estoy trabajando, surgió la necesidad de actualizar muchos registros de la base de datos, específicamente, lo que necesitaba era quitar un texto común dentro de un mismo campo para muchos registros.

Para ser más explícito, tenía algo como esto dentro de una tabla en la base de datos:

id filepath
1 files/archivo1
2 files/archivo2
3 files/archivo3
4 archivo4

Y necesitaba quitar el texto files/ de los registros que lo tuvieran. La magia nos la dará entonces la función REPLACE dentro de una sentencia UPDATE de SQL:

UPDATE {tabla}
SET {campo} = REPLACE({campo}, '{texto original}', '{texto de reemplazo}')
WHERE {campo} LIKE '{texto original}%'

En mi caso, la sentencia sería como sigue:

UPDATE files
SET filepath = REPLACE(filepath, 'files/', '')
WHERE filepath LIKE 'files/%'

Esto hace que primero se filtren los registros cuyo campo filepath comienza por la cadena files/ (nótese el signo de porcentaje solo al final), y luego se reemplace dicho campo por el resultado de la función REPLACE. Esta función toma el valor del campo, busca ahí la cadena files/ y la reemplaza por una cadena vacía (consiguiendo el efecto deseado).

La sentencia WHERE no es completamente necesaria, solo me aseguro que afecte a los registros donde el campo filepath comienza con ese texto y no a los que tengan el texto en otra posición.

Cambiar la contraseña de un usuario en MySQL

Hay varios métodos para cambiar la contraseña de un usuario en MySQL, pero este es el que me parece más seguro ya que no deja rastro de la contraseña antigua ni nueva en el historial de comandos (por lo menos en bash).

Primero, nos logueamos al shell de MySQL con el usuario principal:

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 111
Server version: 5.0.75-0ubuntu10 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Escogemos la base de datos mysql:

use mysql;

Cambiamos los registros de la tabla user que almacenan la contraseña del usuario que queramos:

update user set password=PASSWORD("nuevacontraseña") where user='usuario';

Si quisieramos dejar al usuario con la contraseña en blanco, hacemos lo siguiente:

update user set password=NULL where user='usuario';

Limpiamos y volvemos a cargar los privelegios:

flush privileges;

Y salimos:

mysql> quit

tinyint en MySQl y SQL Server

El tipo de dato tinyint tiene una pequeña diferencia entre ambos motores de BD. Lo común es que siempre ocupa un (1) byte (28 valores posibles, osea, 256). Lo que difiere es que en MySQL se puede marcar como signed o unsigned, lo que hace que pueda o no tomar valores negativos. Cuando es signed, el rango de valores posibles va de -128 hasta 127, y en caso de unsigned, puede tomar desde 0 a 255.

En cambio, en SQL Server (por lo menos en las versiones 2005 y 2008) solo puede tomar valores positivos. Por ello, el rango que puede tomar es de 0 a 255.

Pueden ver la documentación de este tipo de dato aquí para SQL Server 2005 y aquí para MySQL.

SQL Server’s nvarchar(max) on Castle ActiveRecord

I’m using the new SQL Server 2005’s data type nvarchar(max) on a .Net project (ntext has been deprecated in this version) in conjuction with Castle ActiveRecord. For a complete integration (including schema creation), ActiveRecord property should looks like this:

[Property("description", ColumnType = "StringClob", SqlType = "nvarchar(MAX)")]

ColumnType = “StringClob” avoids text to be truncated if it gets too long. SqlType = “nvarchar(MAX)” forces the field to be created as that data type, otherwise, it will be created as nvarchar(255).

A more detailed explanation can be found here and here.

Borrar los transaction logs de SQL Server

El registro de transacciones (transaction log) almacena todas las consultas que se ejecutan en una base de datos, por lo que incluso suele ser más grande que esta. Si por algún motivo queremos limpiar este registro en SQL Server 2005 (lo cual solo debería ocurrir en muy contadas situaciones, como al trabajar con una base de datos de desarrollo), se debe seguir el siguiente procedimiento (previo backup):

BACKUP LOG your_database_name
WITH TRUNCATE_ONLY

Luego de ejecutar este comando, el registro se habrá truncado pero el tamaño del archivo se mantendrá igual; por lo que para reducirlo, se deberá ir al SQL Server Management Studio, escoger la base de datos, clic derecho, Tasks > Shrink > Files. En la siguiente pantalla, escoger Log en File type, darle OK y listo.