aelena.com

6 March 2009

Script para fusionar dos tablas

Filed under: SQL Server, T-SQL — admin @ 10:41

Este script nos permite fusionar dos tablas que ya comparten algunas columnas. La situación es que tenemos una tabla A con, por ejemplo, 50 columnas y una tabla B con 15, algunas de las cuales ya coinciden con algunas de las 50 columnas de la tabla A.

Con este script podemos realizar una “migración” controlada de la tabla A a la B.

   1:  – dos variables para meter los nombres de las tablas
   2:   
   3:  DECLARE @tabla1 varchar(100)
   4:  DECLARE @tabla2 varchar(100)
   5:   
   6:  – tabla origen
   7:   
   8:  SET @tabla1 = N‘tablaOrigen’
   9:   
  10:  – tabla a la que vamos a añadir las columnas
  11:   
  12:  SET @tabla2 = N‘tablaDestino’
  13:   
  14:  – primero la consulta para averiguar las columnas
  15:   
  16:  SELECT
  17:  clmns.name AS [Nombre],
  18:   
  19:  usrt.name AS [DataType],
  20:  ISNULL(baset.name, N‘’) AS [SystemType],
  21:  CAST(CASE WHEN baset.name IN (N‘nchar’, N‘nvarchar’)
  22:  AND clmns.max_length <> -1
  23:  THEN
  24:  clmns.max_length/2
  25:   
  26:  ELSE
  27:   
  28:  clmns.max_length END AS int) AS [Length],
  29:  CAST(clmns.precision AS int) AS [NumericPrecision]
  30:  – tabla temporal donde vamos a guardar los resultados de la consulta
  31:  INTO #_DIFFS
  32:  FROM sys.tables AS tbl
  33:  INNER JOIN
  34:  sys.all_columns AS clmns
  35:  ON clmns.object_id = tbl.object_id
  36:  LEFT OUTER JOIN
  37:  sys.types AS usrt
  38:  ON usrt.user_type_id = clmns.user_type_id
  39:  LEFT OUTER JOIN
  40:  sys.types AS baset
  41:  ON
  42:  baset.user_type_id = clmns.system_type_id
  43:  and
  44:  baset.user_type_id = baset.system_type_id
  45:  WHERE
  46:  clmns.name not in
  47:  (
  48:   
  49:  /* discriminamos auqellas columnas que ya existen en nuestra tabla destino */
  50:  SELECT clmns.name FROM sys.tables as tbl
  51:  INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
  52:  WHERE tbl.[Nombre]=@tabla2
  53:  )
  54:  AND
  55:  – aqui nos ceñimos a ‘dbo’ solamente aunque sería facil
  56:  – de modificar para configurar el esquema como parámetro
  57:  (tbl.name=@tabla1 and SCHEMA_NAME(tbl.schema_id)=N‘dbo’)
  58:  ORDER BY
  59:  clmns.column_id ASC;
  60:   
  61:  – declaramos una serie de variables para el cursor
  62:  DECLARE @columnName VARCHAR(100)
  63:  DECLARE @dataType VARCHAR(100)
  64:  DECLARE @dataLength VARCHAR(100)
  65:  DECLARE @numPres VARCHAR(100)
  66:   
  67:  – y otra variable para un alter table que vamos a usar
  68:  – para añadir la(s) nueva(s) columna(s) a la tabla destino
  69:  DECLARE @alterStatement NVARCHAR(1000)
  70:   
  71:  – declaramos el cursor para la tabla temporal de antes donde hemos
  72:  – guardado el resultado de la consulta
  73:   
  74:  DECLARE _cursor CURSOR FOR
  75:  SELECT [Nombre], DataType, Length, NumericPrecision
  76:  FROM #_DIFFS
  77:   
  78:  – abrimos el cursor
  79:  OPEN _cursor
  80:  FETCH NEXT FROM _cursor INTO
  81:  @columnName, @dataType, @dataLength, @numPres
  82:   
  83:  WHILE (@@FETCH_STATUS <> -1)
  84:  BEGIN
  85:  IF (@@FETCH_STATUS <> -2)
  86:  BEGIN
  87:   
  88:  – en esta variable vamos a ir concatenando una sentencia de sql
  89:  – para crear un ‘alter table add’ que nos permita añadir la nueva columna con — el tipo adecuado
  90:   
  91:  SET @alterStatement = N‘ALTER TABLE ‘ + @tabla2 + N‘ ADD [’
  92:  + @columnName + ‘] ‘ + @dataType
  93:   
  94:   
  95:  – en el caso de que sea VARCHAR o NVARCHAR, indicamos la precisión
  96:  – si fuera decimal o Money o cualquier otro tipo con precisión que — – haya — que indicar también habría que hacerlo, pero — queda como ejercicio ;-) 
  97:  – por eso vemos si el tipo de dato que tenemos guardado en @datatype
  98:  – es VARCHAR o NVARCHAR y realizamos la concatenación adecuada
  99:   
 100:  IF UPPER(@dataType) = ‘VARCHAR’ OR UPPER(@dataType) = ‘NVARCHAR’
 101:  BEGIN
 102:      SET @alterStatement = @alterStatement + ‘(’ + @dataLength + ‘)’
 103:  END
 104:   
 105:  
 106:  – si se quiere ver la concatenación…
 107:  – PRINT @alterStatement;
 108:   
 109:  – finalmente ejecutamos la sentencia
 110:   
 111:  exec sp_executesql @alterStatement;
 112:   
 113:  END;
 114:   
 115:  – y recogemos la siguiente fila
 116:   
 117:  FETCH NEXT FROM tablesize_cursor INTO @columnName, @dataType, @dataLength, @numPres
 118:  END;
 119:   
 120:  
 121:  – finalmente cerramos y limpiamos el cursor
 122:  CLOSE tablesize_cursor;
 123:  DEALLOCATE tablesize_cursor;
 124:  – tiramos la tabla temporal que hemos usado
 125:  DROP TABLE #_DIFFS
 126:   
 127: 

Este script o uno similar nos puede salvar mucho tiempo cuando tengamos que integrar dos bases de datos diferentes con considerables diferencias entre las tablas de una y otra. Quizás haya otras maneras, como SSIS, pero a mí me gusta tener el control y aquí podemos tener el control al 100 por 100 de una manera sencilla y rápida.

 

Simplemente parametrizando los nombres de las tablas al principio podemos reutilizar el script en múltiples situaciones. Y la lógica dentro del cursor es muy sencilla de adaptar a cualquier necesidad.

 

Saludos

4 August 2008

Operaciones masivas

Filed under: DBA, SQL Server, T-SQL — admin @ 11:36

Imaginaos que os dicen que asigneis permisos a un usuario  a 400 procedimientos almacenados o algo asi que hay en una base de datos

Pues con un script como este, podemos buscarnos la vida para automatizar la tarea

   1:  DECLARE @spName AS VARCHAR(150)
   2:  DECLARE @command AS NVARCHAR(400)
   3:  CREATE TABLE #TBLSUMMARY ( sentence varchar(400) );
   4:  DECLARE procs_cursor CURSOR FOR
   5:  Select name from sys.procedures
   6:  where [type] = ‘P’
   7:  and is_ms_shipped = 0
   8:  and name not like ’sp_%’
   9:  order by name
  10:  OPEN procs_cursor
  11:  FETCH NEXT FROM procs_cursor INTO @spName;
  12:  WHILE (@@FETCH_STATUS <> -1)
  13:  BEGIN
  14:  IF (@@FETCH_STATUS <> -2)
  15:  BEGIN
  16:  
  17:  – se pueden generar las instrucciones
  18:  INSERT INTO #TBLSUMMARY VALUES
  19:  ( ‘deny execute on ‘ + @spName +
  20:  ‘ to [USUARIO]’)
  21:  
  22:  – y tambien ya de paso ejecutarlas
  23:  SET @command = N‘deny execute on ‘ + @spName +
  24:  ‘ to [USUARIO]’;
  25:  PRINT @command
  26:  exec sp_executesql @command ;
  27:  
  28:  END;
  29:  FETCH NEXT FROM procs_cursor INTO @spName;
  30:  END;
  31:  CLOSE procs_cursor;
  32:  DEALLOCATE procs_cursor;
  33:  
  34:  SELECT * FROM #TBLSUMMARY;
  35:  
  36:  DROP TABLE #TBLSUMMARY;
  37: 

Mediante un cursor elegimos todos los procedimientos almacenados de usuario de una base de datos, y mediante las dos sentencias dentro del bucle while aprovechamos tanto para generar un script como para ejecutar las sentencias que vamos generando sobre la marcha.En este ejemplo estamos denegando permisos a un usuario concreto a ejecutar cualquier procedimiento almacenado de la base de datos.

Generamos las instrucciones de forma dinámica y las guardamos en la tabla temporal, para luego hacer un select de la misma y quedarnos con un script que podemos tener que pasar a la gente encargada de pasar a producción por ejemplo.

Aprovechamos igualmente, mediante sp_executesql, para ejecutar la sentencia, aunque también podriamos generar el script y correrlo igualmente.

Este script ademas lo podemos aprovechar para adaptarlo a multiples situaciones cambiando la Select del cursor y la operacion que hacemos dentro. No hay que olvidar que tal vez necesitemos redefinir la tabla temporal, según la tarea que vayamos a realizar.

code on!!!!

1 August 2008

Averigua las veces que se ejecutan tus SPs

Filed under: DataBase, DBA, SQL Server — admin @ 11:34

Con esta consulta podemos ver (en SQL Server 2005) el número de ejecuciones de nuestros procedimientos almacenados de una base de datos que indicamos en la línea 7:

   1:  SELECT DB_NAME(st.dbid) DBName
   2:  ,OBJECT_NAME(st.objectid) StoredProcedure
   3:  ,max(cp.usecounts) Execution_count
   4:  FROM sys.dm_exec_cached_plans cp
   5:  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
   6:  where DB_NAME(st.dbid) is not null
   7:  and cp.objtype = ‘proc’
   8:  AND DB_NAME(st.dbid) = ‘NOMBRE DE TU BASE DE DATOS’
   9:  group by cp.plan_handle, DB_NAME(st.dbid),
  10:  OBJECT_NAME(objectid)
  11:  order by max(cp.usecounts)

Si queremos las estadísticas para todas las bases de datos del servidor, comentad la línea 7.

Saludos

Averigua el tamaño de las filas de tus tablas

Filed under: DataBase, DBA, Filas, Row Size, SQL Server, T-SQL, Tamaño Filas — admin @ 11:33

Este es un procedimiento que nos dirá cuanto “pesan” las filas de nuestras tablas según las hayamos definido. Nos puede resultar útil para preparar nuestras bases de datos para unos volúmenes de datos determinados.

   1:   
   2:  CREATE PROCEDURE [dbo].[FindRowSize]
   3:  AS
   4:  BEGIN
   5:  DECLARE @rowsize int;
   6:  DECLARE @lineaSize int;
   7:  DECLARE @anexoSize int;
   8:  SET NOCOUNT ON;
   9:  CREATE TABLE #TBLSUMMARY ( tablename varchar(75), rowsize int );
  10:  DECLARE tablesize_cursor CURSOR FOR
  11:  SELECT name FROM sys.tables WHERE type_desc = ‘USER_TABLE’
  12:  OPEN tablesize_cursor
  13:  DECLARE @tablename sysname;
  14:  FETCH NEXT FROM tablesize_cursor INTO @tablename;
  15:  WHILE (@@FETCH_STATUS <> -1)
  16:  BEGIN
  17:  IF (@@FETCH_STATUS <> -2)
  18:  BEGIN
  19:   
  20:  SET @rowsize = (select sum(max_length) from sys.columns where
  21:  object_id in ( select object_id from sys.tables where name=@tablename ));
  22:   
  23:  INSERT INTO #TBLSUMMARY VALUES (@tablename,@rowsize);
  24:   
  25:  END;
  26:  FETCH NEXT FROM tablesize_cursor INTO @tablename;
  27:  END;
  28:  CLOSE tablesize_cursor;
  29:  DEALLOCATE tablesize_cursor;
  30:   
  31:  SELECT * FROM #TBLSUMMARY;
  32:  DROP TABLE #TBLSUMMARY;

Se hace un cursor sobre la colección de tablas de usuario y se suman las longitudes de cada uno de los campos.

Se va guardando todo en una tabla temporal que consultamos una vez terminado todo el proceso.

Saludos