jueves, 5 de noviembre de 2015

Aplicando un Filtro a sp_MSforeachDB

Problema
Cuando se necesita trabajar sobre multiples bases de datos en una misma instnacia, a veces, se necesita ejecutar una consulta en cada una de estas, sí estamos utilizando el procedimiento almacenado no documentado sp_MSforeachDB, nos encontraremos en ciertos casos en la obligación de excluir o filtrar algunas de estas bases datos, por ejemplos las bases de datos del sistema (master, tempdb, model y msdb) o cualquier otra base de datos que por una razón u otra no aplique para el caso.


Solución
USE MASTER;
GO
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''master'',''tempdb'',''model'',''msdb'')
BEGIN
       SELECT name, physical_name,state,size
       FROM ?.sys.database_files
END';

GO


Referencias

martes, 28 de abril de 2015

Cómo iterar en un conjunto de resultados mediante T-SQL en SQL Server

Problema:

Describir los diferentes métodos que se pueden usar para simular la lógica iterativa de un cursor en un procedimiento almacenado (stored procedure), disparador (trigger) o lote (batch) de T-SQL.

Solución:

Existen tres métodos para iterar a través de un conjunto de resultados utilizando sentencias Transact-SQL.

Un método es el uso de tablas temporales. Con este método, usted crea una “instantánea” (snapshot) de la sentencia SELECT inicial y utilizar esta como base sobre la cual iterar. Por ejemplo:

/********** ejemplo 1 **********/ 
DECLARE @au_id CHAR( 11 )

SET ROWCOUNT 0
SELECT * INTO #mytemp FROM authors

SET ROWCOUNT 1

SELECT @au_id = au_id FROM #mytemp

WHILE @@rowcount <> 0
BEGIN
    SET ROWCOUNT 0
    
SELECT * FROM #mytemp WHERE au_id = @au_id
    
DELETE #mytemp WHERE au_id = @au_id

    
SET ROWCOUNT 1
    
SELECT @au_id = au_id FROM #mytemp

END
SET
ROWCOUNT 0
GO


Un segundo método es utilizar la función min para caminar una tabla una fila a la vez. Este método toma en cuenta las filas que se agregan después de la ejecución del procedimiento almacenado empieza, si la fila nueva posee un identificador único mayor que el de la fila que se está siendo procesada en dicho momento por la consulta. Por ejemplo:

/********** ejemplo 2 **********/ 

DECLARE @au_id CHAR( 11 )

SELECT @au_id = MIN( au_id ) FROM authors

WHILE @au_id IS NOT NULL
BEGIN
    SELECT
* FROM authors WHERE au_id = @au_id
    
SELECT @au_id = MIN( au_id ) FROM authors WHERE au_id > @au_id

END
GO

NOTA: ambos ejemplos 1 y 2 asumen que una columna de identificador único existe para cada fila en la tabla fuente. En algunos casos, dicho identificador no existe. Si ese es el caso, usted puede modificar el método de tabla temporal para usar una columna identificadora nueva. Por ejemplo:

/********** ejemplo 3 **********/ 

SET ROWCOUNT 0
SELECT NULL mykey, * INTO #mytemp FROM authors

SET ROWCOUNT 1
UPDATE #mytemp SET mykey = 1

WHILE @@rowcount > 0
BEGIN
    SET
ROWCOUNT 0
    
SELECT * FROM #mytemp WHERE mykey = 1
    
DELETE #mytemp WHERE mykey = 1
    
SET ROWCOUNT 1
    
UPDATE #mytemp SET mykey = 1

END
SET
ROWCOUNT 0
GO


Referencias:

How to iterate through a result set by using Transact-SQL in SQL Server

Script para identificar los índices que no están siendo utilizados en SQL Server

Problema:

Script para identificar los índices que no están siendo utilizados en SQL Server.

Solución:

SELECT TOP 25
    o.name
AS ObjectName

    i.name AS IndexName
    i.index_id AS IndexID
    dm_ius.user_seeks AS UserSeek
    dm_ius.user_scans AS UserScans
    dm_ius.user_lookups AS UserLookups
    dm_ius.user_updates AS UserUpdates
    p.TableRows
    'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' +                    
    QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
    INNER JOIN sys.indexes i 
        ON i.index_id = dm_ius.index_id 
        AND dm_ius.OBJECT_ID = i.OBJECT_ID
    INNER JOIN sys.objects o 
        ON dm_ius.OBJECT_ID = o.OBJECT_ID
    INNER JOIN sys.schemas s 
        ON o.schema_id = s.schema_id
    INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
                FROM sys.partitions p 
                GROUP BY p.index_id, p.OBJECT_ID) p
        ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') =
    AND dm_ius.database_id = DB_ID()
    AND
i.type_desc = 'nonclustered'

    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO


Referencias:


SQL SERVER – 2008 – Unused Index Script – Download

Scripts para obtener la información de seguridad de SQL Server

Problema:

Encontrar la información de seguridad referente a los accesos (logins), usuarios y roles y permisos a nivel de objetos en las bases de datos en SQL Server.

Solución:

Script que obtiene los logins de servidor y los roles asignados a estos:

SELECTa.name AS LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,CASE
    
WHEN b.sysadmin = 1 THEN 'sysadmin'
    
WHEN b.securityadmin=1 THEN 'securityadmin'
    
WHEN b.serveradmin=1 THEN 'serveradmin'
    
WHEN b.setupadmin=1 THEN 'setupadmin'
    
WHEN b.processadmin=1 THEN 'processadmin'
    
WHEN b.diskadmin=1 THEN 'diskadmin'
    
WHEN b.dbcreator=1 THEN 'dbcreator'
    
WHEN b.bulkadmin=1 THEN 'bulkadmin'ELSE 'Public' END AS 'ServerRole'FROM sys.server_principals a  JOIN MASTER..syslogins b
    
ON a.sid=b.sid WHERE a.TYPE  <> 'R'
      
AND a.name NOT LIKE '##%'

GO

Script que obtiene los usuarios de base de datos y los roles asignados a estos:

DECLARE @DBuser_sql VARCHAR(4000);DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200));

SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
;


INSERT @DBuser_tableEXEC sp_MSforeachdb @command1 = @dbuser_sql
  
SELECT * FROM @DBuser_table ORDER BY DBName;

GO

Script que obtiene los permisos a nivel de objeto de los usuarios de base de datos:

DECLARE @Obj_sql VARCHAR(2000);
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200));

SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '
;


INSERT @Obj_table
  
EXEC sp_msforeachdb @command1 = @Obj_sql
      
SELECT * FROM @Obj_table;

GO


Referencias:

SCRIPT TO RETRIEVE SECURITY INFORMATION SQL SERVER 2005 AND ABOVE

Encontrar el folder por defecto para las copias de respaldo (backups) en SQL Server.

Problema:

Encontrar el folder por defecto para las copias de respaldo (backups) en SQL Server.

Solución:

DECLARE @ruta NVARCHAR(4000);

EXEC MASTER.dbo.xp_instance_regread
        
N'HKEY_LOCAL_MACHINE',
        
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
        
@ruta OUTPUT
        
'no_output';
SELECT @ruta;
   

Referencias:

Function to Return Default SQL Server Backup Folder

Obtener información sobre núcleos físicos, CPUs físicos y virtuales y tipo de procesador en SQL Server utilizando T-SQL

Descripción:

Script para obtener la información sobre los procesadores disponibles para ser consumidos por SQL Server:
  • Número total de CPUs físicos
  • Número total de núcleos por CPU
  • Número total de núcleos físicos
  • Número total de núcleos virtuales
  • Tipo de procesador (x86 o x64)
Solución:

DECLARE @xp_msver TABLE (
    
[idx] [int] NULL
    ,
[c_name] [varchar](100) NULL
    ,
[int_val] [float] NULL
    ,
[c_val] [varchar](128) NULL
    )
INSERT INTO @xp_msverEXEC ('[master]..[xp_msver]');;
WITH [ProcessorInfo]AS (
    
SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
        
,CASE
            
WHEN hyperthread_ratio = cpu_count
                
THEN cpu_count
            
ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
            
END AS [number_of_cores_per_cpu]
        
,CASE
            
WHEN hyperthread_ratio = cpu_count
                
THEN cpu_count
            
ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
            
END AS [total_number_of_cores]
        
,[cpu_count] AS [number_of_virtual_cpus]
        
,(
            
SELECT [c_val]
            
FROM @xp_msver
            
WHERE [c_name] = 'Platform'
            
) AS [cpu_category]
    
FROM [sys].[dm_os_sys_info]
    
)SELECT [number_of_physical_cpus]
    
,[number_of_cores_per_cpu]
    
,[total_number_of_cores]
    
,[number_of_virtual_cpus]
    
,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]FROM [ProcessorInfo]


Referencias:

Get SQL Server Physical Cores, Physical and Virtual CPUs, and Processor type information using Transact-SQL (T-SQL) script

viernes, 20 de febrero de 2015

Obtener la cuenta de servicio de Microsoft SQL Server con T-SQL

Descripción:
La información de la cuenta del servicio de SQL Server es almacenada en la base de datos del Registro de Windows. Usted puede acceder a dicha información desde la Consola de Servicios (Services Console) o el Administrador de Configuración de SQL Server (SQL Server Configuration Manager).

Solución:
Para obtener la información de la cuenta desde la Consola de Servicios:
  1. Ir a Inicio > Ejecutar > services.msc
  2. Click derecho sobre el Servicio SQL Server, ej. "SQL Server (InstanceName) e ir a Propiedades
  3. La información de la cuenta esta disponible en la pestaña Log On:

También podemos obtener esta información usando T-SQL, podemos utilizar el procedimiento almacenado extendido del sistema xp_instance_regread para leer el Registro de Windows. Utilice el código siguiente para obtener la información de las cuentas del los servicios SQL Server y SQL Server Agent:

DECLARE       @sql_server_service_account         SYSNAME;DECLARE       @sql_server_agent_service_account   SYSNAME;
EXECUTE       MASTER.dbo.xp_instance_regread
              
@rootkey      = N'HKEY_LOCAL_MACHINE',
              
@key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
              
@value_name   = N'ObjectName',
              
@value        = @sql_server_service_account OUTPUT
EXECUTE       MASTER.dbo.xp_instance_regread
              
@rootkey      = N'HKEY_LOCAL_MACHINE',
              
@key          = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
              
@value_name   = N'ObjectName',
              
@value        = @sql_server_agent_service_account OUTPUT
SELECT [SQL Server Service Account] = @sql_server_service_account,
      
[SQL Server Agent Service Account] = @sql_server_agent_service_account;GO


Desde Microsoft SQL Server 2008 R2 SP1 y superior una nueva vista de administración dinámica (DMV) esta disponible sys.dm_server_services la cual retorna la información de las cuentas de servicio de todas las instancia. Esta vista también retorna información adicional acerca de cada servicio como tipo de inicio, process id actual, nombre físico del ejecutable. Para consultar espec&iacuteficamente las cuentas de los servicios SQL Server y SQL Server Agent con esta vista podemos utilizar la siguiente consulta:

SELECT servicename, service_accountFROM   sys.dm_server_services
GO


Referencias

jueves, 19 de febrero de 2015

Crecimiento del tamaño de la base de datos como una lista

Descripción:

Este script de Transact-SQL usa la historia de copias de respaldo para analizar el crecimiento del tamaño de las bases de datos sobre un periodo determinado de tiempo. También se calcula adicionalmente el mínimo, máximo y promedio de crecimiento de tamaño mensual en relación al mes anterior. Estos valores son útiles para el planeamiento futuro de recursos de almacenamiento y sistemas de copias de seguridad. Este script trabaja en Microsoft SQL Server 2005 y versiones superiores en todas las ediciones.


Requerimientos:

  • Este script requiere acceso y permiso de lectura sobre la base de datos del sistema msdb.


Solución:

-- Script T-SQL para analizar el crecimiento de tamaño de la base de datos utilizando la historia de copias de respaldo.
DECLARE @endDate DATETIME, @months smallint;
SET @endDate = GETDATE();    -- Incluir las estadísticas de las copias de respaldo de hoy.
SET @months = 6;            -- hasta 6 meses atrás.

;WITH HIST AS
  
(SELECT BS.database_name AS DatabaseName
          
,YEAR(BS.backup_start_date) * 100
          
+ MONTH(BS.backup_start_date) AS YearMonth
          
,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB
          
,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB
          
,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
    
FROM msdb.dbo.backupset AS BS
        
INNER JOIN
        
msdb.dbo.backupfile AS BF
            
ON BS.backup_set_id = BF.backup_set_id
    
WHERE NOT BS.database_name IN
              
('master', 'msdb', 'model', 'tempdb')
          AND
BF.file_type = 'D'
          
AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
    
GROUP BY BS.database_name
            
,YEAR(BS.backup_start_date)
            ,
MONTH(BS.backup_start_date))SELECT MAIN.DatabaseName
      
,MAIN.YearMonth
      
,MAIN.MinSizeMB
      
,MAIN.MaxSizeMB
      
,MAIN.AvgSizeMB
      
,MAIN.AvgSizeMB
      
- (SELECT TOP 1 SUB.AvgSizeMB
          
FROM HIST AS SUB
          
WHERE SUB.DatabaseName = MAIN.DatabaseName
                
AND SUB.YearMonth < MAIN.YearMonth
          
ORDER BY SUB.YearMonth DESC) AS GrowthMBFROM HIST AS MAINORDER BY MAIN.DatabaseName
        
,MAIN.YearMonth;
GO    



Referencias

Mover los archivos de base de datos de SQL Server a otra ubicación vía T-SQL.

SQL Server 2000

-- Cambiar al contexto de la base de datos master
USE MASTER;
GO

-- Llevar la base de datos a single user mode
-- Esto termina todas las conexiones a la base de datos
ALTER DATABASE dbTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Desacoplar la base de datos
EXEC MASTER.dbo.sp_detach_db @dbname = N'dbTest'
GO

--
-- Mover el o los archivos físicos de la base de datos manualmente a la nueva ruta.
-- ej.: x:\move e:\dbTest.mdf e:\NuevaRuta\dbTest.mdf
--

-- Re-acoplar la base de datos
CREATE DATABASE [TestDB] ON
(FILENAME = N'E:\RutaNueva\dbTest.mdf' ),
(
FILENAME = N'E:\RutaNueva\dbTest_log.ldf' )
FOR ATTACH
GO

SQL Server 2005, 2008, 2008 R2 o superior (Misma instancia)

-- Cambiar al contexto de la base de datos master
USE MASTER;
GO

-- Cambiar el estado de la base de datos a modo de usuario único
ALTER DATABASE dbTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Cambiar el estado de la base de datos a fuera de línea
ALTER DATABASE dbtest SET OFFLINE;
GO

--
-- Mover el o los archivos físicos de la base de datos manualmente a la nueva ruta.
-- ej.: x:\move e:\dbTest.mdf e:\NuevaRuta\dbTest.mdf
--

-- Re-apunta la ruta del archivo mdf a la nueva ubicación
ALTER DATABASE dbTest
MODIFY
FILE
  
(NAME='dbTest', FILENAME='E:\NuevaRuta\dbTest.mdf');

-- Re-apunta la ruta del archivo ldf a la nueva ubicación
ALTER DATABASE dbTest
MODIFY
FILE
  
(NAME='dbTest_Log', FILENAME='E:\NuevaRuta\dbTest_log.ldf');
GO

-- Cambiar el estado de la base de datos a en-línea
ALTER DATABASE TEMP SET ONLINE;
GO

-- Cambiar el estado de la base de datos a modo multi-usuario
ALTER DATABASE TEMP SET MULTI_USER;
GO

SQL Server 2005, 2008, 2008 R2 o superior (Instancia diferente)

-- Cambiar al contexto de la base de datos master
USE MASTER;
GO

-- Desacoplar la base de datos
EXEC sp_detach_db @dbname = N'dbTest';

--
-- Mover el o los archivos físicos de la base de datos manualmente a la nueva ruta.
-- ej.: x:\move e:\dbTest.mdf e:\NuevaRuta\dbTest.mdf
--

-- Reacoplar la base de datos con los archivos en la nueva instancia y nueva ubicación
CREATE DATABASE dbTest
      
ON(NAME='dbTest',
            
FILENAME='e:\NuevaRuta\dbTest.mdf')
      
LOG ON(NAME='MyDatabase_Log',
            
FILENAME='e:\NuevaRuta\dbTest_log.ldf')
      
FOR ATTACH
      
WITH ENABLE_BROKER;
GO

Referencias