aelena.com

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