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

• SQL Server: Applying Filter on sp_MSforeachDB

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') = 1 
    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