DESHABILITAR BASE DE DATOS

DESHABILITAR BASE DE DATOS

DISABLE DATABASE SQL SERVER 

ALTER DATABASE [REMATE_REPLICA] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT;  -- PARA EVITAR QUE ÉSTE PROCESO SE CONECTE EN SEGUNDO PLANO

GO

ALTER DATABASE [REMATE_REPLICA] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

USE MASTER;

GO

ALTER DATABASE [REMATE_REPLICA] SET OFFLINE;

CODE SNIPPET - Layout - Template

El siguiente script es un ejemplo de una plantilla para *.snippet:

UNA VEZ QUE SE TIENE LA PLANTILLA (EL CÓDIGO A INSERTAR ES LO GRIS)
SE GUARDA EL ARCHIVO COMO .SNIPPET Y SE SUBE ASÍ...

EJEMPLO 

<?xml version="1.0" encoding="utf-8" ?>

<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">

<CodeSnippet Format="1.0.0">

<Header>

<Title>Encabezado</Title>

<Description> Encabezado </Description>

<Author> Manuel Omar Olguín Hernández </Author>

<SnippetTypes>

<SnippetType>Expansion</SnippetType>

</SnippetTypes>

</Header>

<Snippet>

<Declarations>

<Literal>

<ID>Encabezado</ID>

<ToolTip>Convenciones</ToolTip>

<Default>Convenciones</Default>

</Literal>

</Declarations>

<Code Language="SQL">

<![CDATA[

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===============================================================================

-- Autor:                 NOMBRE DEL AUTOR

-- Fecha:                 YYYY / MM / DD

-- Versión:                1.0

-- Requerimiento:  

-- Descripcion:           

-- ==============================================================================

]]>

</Code>

</Snippet>

</CodeSnippet>

</CodeSnippets>

UNPIVOT - Columnas a Filas

El siguiente script ayuda a pasar información que está en columnas en filas, respetando el nombre de la columna y su valor, para ello se deben convertir todos los valores de las n columnas en un solo tipo de dato para que la función UNPIVOT funcione correctamente.


se crea la tabla como variable:

DECLARE @T1 AS TABLE (ID   INT, NAME VARCHAR(30), FECHA  DATE, OTRA VARCHAR(MAX))

INSERT INTO @T1

SELECT 1, 'OMAR', GETDATE(), 'xxxxxxxxxxx' UNION ALL

SELECT 2, 'MANUEL', '2016-05-05', '*************';

select * from @T1;

el resultado es el siguiente:

Ahora lo que se quiere es pasar esos registros con las columnas en filas.

así:

para lograrlo se ejecuta la función UNPIVOT Asi:

WITH TABLA AS

    (

        -- Common table Expression para convertir todos los datos en un solo tipo

        SELECT

                     ID

                    ,NAME =  CONVERT(VARCHAR(MAX), Name)

                    ,FECHA = CONVERT(VARCHAR(MAX), FECHA)

                    ,OTRA  = CONVERT(VARCHAR(MAX), OTRA)

        FROM @T1

       )

SELECT

              ID

             ,columna

             ,valor

FROM TABLA

unpivot

       (

           valor for columna in ([name],[fecha],[otra])

          ) as unpvt

esto fue un ejemplo simple.

REORGANIZE OR REBUILD INDEX

-- =========================================================================================

-- AUTOR:           MANUEL OMAR OLGUÍN HERNÁNDEZ

-- FECHA            15-JUL-2013

-- DESCRIPCIÓN      REORGANIZA O RECONSTRUYE LOS ÍNDICES DE LA BASES DE DATOS QUE CONTENGAN FRAGMENTACIÓN SOBRE SUS TABLAS ENTRE

-- 5  - 30 % REORGANIZE

-- 31 - 100% REBUILD

--

-- NOTA:            EN LA BASE SELECCIONADA SEGÚN EL CONTEXTO

-- =========================================================================================

USE DATABASENAME;

GO

 SELECT I.NAME AS INDICE , T.NAME AS TABLENAME, S.NAME AS SCHEMANAME, AVG_FRAGMENTATION_IN_PERCENT

             ,IND.INDEX_TYPE_DESC

             ,IND.DATABASE_ID

             ,IND.INDEX_ID

             ,DB.NAME AS DATABASE_NAME

             ,ID = ROW_NUMBER () OVER (ORDER BY I.NAME, IND.INDEX_ID)

 INTO #INDICES

 FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (NULL,null,NULL,NULL,NULL)AS IND

 INNER JOIN SYS.INDEXES AS I ON IND.OBJECT_ID = I.OBJECT_ID

 INNER JOIN SYS.TABLES AS T ON IND.OBJECT_ID = T.OBJECT_ID

 INNER JOIN SYS.SCHEMAS AS S ON T.SCHEMA_ID = S.SCHEMA_ID

 INNER JOIN SYS.DATABASES AS DB ON IND.DATABASE_ID = DB.DATABASE_ID

 WHERE IND.INDEX_ID > 0 AND I.NAME IS NOT NULL

 AND IND.AVG_FRAGMENTATION_IN_PERCENT BETWEEN 5 AND 100

 -- SELECT * FROM #INDICES order by ID

 DECLARE @ID               INT

 DECLARE @INDICE           NVARCHAR(500)

 DECLARE @INDEX_ID         INT

 DECLARE @SCHEMANAME       VARCHAR(200);

 DECLARE @TABLENAME        VARCHAR(200);

 DECLARE @STRSQL           NVARCHAR(500);

 DECLARE @DESCRIPTION      NVARCHAR (500);

 DECLARE @INDEX_TYPE       NVARCHAR (50);

 DECLARE @INSTRUCCION      NVARCHAR(150);

 DECLARE @FRAGMENTATION    DECIMAL (8,2)

 SET @ID =0

 WHILE (@ID IS NOT NULL)

  BEGIN

  /*

   SET @SCHEMANAME  = (SELECT TOP 1 SCHEMANAME                                  FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

   SET @TABLENAME   = (SELECT TOP 1 TABLENAME                                   FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

   SET @INDICE             = (SELECT TOP 1 INDICE                                             FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

   SET @DESCRIPTION = (SELECT TOP 1 AVG_FRAGMENTATION_IN_PERCENT   FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

   */

   IF @ID IS NOT NULL

             BEGIN

                SELECT           TOP 1

                                        @SCHEMANAME         = SCHEMANAME,

                                        @TABLENAME          = TABLENAME,

                                        @INDICE                    = INDICE,

                                        @FRAGMENTATION      = AVG_FRAGMENTATION_IN_PERCENT,

                                        @INDEX_TYPE         = INDEX_TYPE_DESC,                           

                                        @INDEX_ID           = INDEX_ID

                FROM                    #INDICES

                WHERE ID > @ID

                ORDER BY ID

                SET @ID   = (SELECT TOP 1 ID                                    FROM #INDICES WHERE ID > @ID ORDER BY ID )

                IF @FRAGMENTATION <= 30 SET @INSTRUCCION = 'REORGANIZE';

                          

                      --IF @FRAGMENTATION > 30  SET @INSTRUCCION = 'REBUILD';

                IF @FRAGMENTATION > 30  SET @INSTRUCCION = 'REBUILD PARTITION = ALL WITH (FILLFACTOR = 85)';

                SET @STRSQL             = ('ALTER INDEX ' + @INDICE + ' ON ' + @SCHEMANAME + '.' + @TABLENAME + ' ' + @INSTRUCCION)

                SET @DESCRIPTION = 'ID:' + CONVERT(VARCHAR, @ID) +  '  INDEX:' + CONVERT(VARCHAR,@INDEX_ID) + ' ' + @STRSQL + '   - ' + @INDEX_TYPE  + '   AVG FRAGMENTATION:' + CONVERT(VARCHAR,@FRAGMENTATION);

                RAISERROR (@DESCRIPTION,0,1) WITH NOWAIT

                EXECUTE SP_EXECUTESQL @STRSQL

             END

   

 END

 

   SELECT I.NAME AS INDICE , T.NAME AS TABLENAME, S.NAME AS SCHEMANAME, AVG_FRAGMENTATION_IN_PERCENT

             ,IND.INDEX_TYPE_DESC

             ,IND.DATABASE_ID

             ,IND.INDEX_ID

             ,DB.NAME AS DATABASE_NAME

             ,ID = ROW_NUMBER () OVER (ORDER BY I.NAME, IND.INDEX_ID)

 INTO #INDICES_REBUILD

 FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (NULL,null,NULL,NULL,NULL)AS IND

 INNER JOIN SYS.INDEXES AS I ON IND.OBJECT_ID = I.OBJECT_ID

 INNER JOIN SYS.TABLES AS T ON IND.OBJECT_ID = T.OBJECT_ID

 INNER JOIN SYS.SCHEMAS AS S ON T.SCHEMA_ID = S.SCHEMA_ID

 INNER JOIN SYS.DATABASES AS DB ON IND.DATABASE_ID = DB.DATABASE_ID

 WHERE IND.INDEX_ID > 0 AND I.NAME IS NOT NULL

 AND IND.AVG_FRAGMENTATION_IN_PERCENT BETWEEN 0 AND 100

 SELECT             A.Indice, A.SchemaName, A.TableName

                    ,OLD_FRAGMENTATION = CONVERT(DECIMAL(5,2),(A.AVG_FRAGMENTATION_IN_PERCENT))

                    ,NEW_FRAGMENTATION = CONVERT(DECIMAL(5,2),(B.AVG_FRAGMENTATION_IN_PERCENT))

                    ,Fragmentation = CONVERT(VARCHAR(10), CONVERT(DECIMAL(5,2), (A.AVG_FRAGMENTATION_IN_PERCENT)  - (B.AVG_FRAGMENTATION_IN_PERCENT))) + '%'

                    ,A.INDEX_ID 

                    ,A.INDEX_TYPE_DESC AS INDEX_TYPE

                    ,A.DATABASE_NAME

                   

 FROM        #INDICES                   AS A

 LEFT JOIN   #INDICES_REBUILD    AS B ON A.TABLENAME = B.TABLENAME AND A.SCHEMANAME = B.SCHEMANAME AND A.DATABASE_ID = B.DATABASE_ID AND A.INDEX_ID = B.INDEX_ID AND A.INDICE = B.INDICE

 ORDER BY A.SCHEMANAME, A.TABLENAME, A.INDICE, A.INDEX_ID

 DROP TABLE #INDICES;

 DROP TABLE #INDICES_REBUILD

  /* To rebuild all indexes in a table changing the fillfactor (cuando se hace el rebuild conviene ajustar el fill factor de nuevo, por si ya está lleno)*/

--ALTER INDEX ALL ON Avispa.Brokers

--REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);