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