Librerías de código TSQL

Seguramente muchas veces nos hemos encontrado duplicando procedimientos, vistas, etc. a lo largo de varias bases de datos de un mismo servidor. Esto puede producirse por ejemplo cuando tenemos un conjunto de procedimientos “útiles” que reutilizamos en diferentes bases de datos.

Otro caso habitual es cuando tenemos copias de una misma base de datos en un servidor para gestionar diferentes clientes. Es decir, tenemos un esquema de base de datos y sus objetos de bases de datos asociados en común pero con datos independientes.

El problema en ambos casos es que necesitaríamos ejecutar un procedimiento almacenado o acceder a una vista desde el contexto de otra base de datos. SQL Server 2005 utiliza otra aproximación para los objetos y vistas de sistema dentro del espacio de nombres sys diferente a SQL 2000. Existe una nueva base de datos oculta llamada Resource Database. Todos los objetos de ésta aparecen de forma transparente en cada una de las bases de datos. De esta forma se facilita y asegura, por ejemplo, la actualización en un Service Pack de todos los procedimientos almacenados en todas las bases de datos de la instancia.

Desafortunadamente actualmente no contamos con la posibilidad de definir nuestras bases de datos de recursos de forma que pudiéramos personalizar nuestros servidores con nuestras propias librerías TSQL. Es por ello que vamos a comentar otra alternativa que es válida en algunos casos.

Básicamente lo que haremos será utilizar procedimientos almacenados de sistema en la base de datos master y la nueva funcionalidad de sinónimos de SQL Server 2005.

Si nos centramos en el segundo de los escenarios comentados anteriormente, es muy probable que ya dispongamos de una aplicación que está funcionando contra una capa de procedimientos almacenados en la base de datos y no queremos o podemos cambiar el código fuente de la aplicación. En este caso podríamos seguir el siguiente procedimiento para unificar todos los procedimientos de las N bases de datos en la base de datos master e utilizarlos desde cada una de ellas de forma transparente. Los pasos a seguir serían:

Para la realización de los pasos 2 a 4 podemos hacerlo con un script similar al siguiente:

use master

declare @database nvarchar(max)

declare @name nvarchar(max)

declare @newname nvarchar(max)

declare @sql nvarchar(max)

declare cur cursor for

select o.name from sys.objects o

      join sys.schemas s on o.schema_id=s.schema_id

      where type=‘P’

            and o.name not like ‘sp%’ — Ajustar para devolver los procedimientos correspondientes

            and s.name = ‘dbo’              — Ajustar para devolver los procedimientos correspondientes

set @database=‘nuestrabasededatos’ — El nombre de la base de datos de la que migramos los procedimientos

open cur

fetch next from cur into @name

while @@fetch_status=0

begin

      set @newname=replace(@name,‘.’,‘.sp’)

      select @name,@newname

      exec sp_rename @name, @newname

      EXEC sys.sp_MS_marksystemobject @newname

      set @sql= ‘use ‘ + @database +‘; exec sp_rename ”’ + @name + ”’ ,”’ + @name+‘_borrar’ +”’ ; create synonym ‘ + @name + ‘ for ‘ + @newname

      select @sql

      EXECUTE sp_executesql @sql

      fetch next from cur into @name

end

close cur

deallocate cur

Debemos tener en cuenta que no existen soluciónes únicas y que no siempre son aplicables. Hoy en día existen también otras formas de mantener esta coherencia de objetos de bases de datos. Por ejemplo manteniendo un repositorio en SourceSafe y sincronizando éste contra cada base de datos con Visual Studio 2005 Team Edition for Database Professionals.