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);

CREAR USUARIO

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

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

-- FECHA:       1 JUN 2015

-- DESCRIPCION  CREA UN LOGIN Y SU CUENTA DE USUARIO 

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

IF NOT EXISTS(SELECT * FROM SYS.syslogins WHERE NAME = 'UserName')

       BEGIN

             CREATE LOGIN UserName

             WITH PASSWORD = 'P@$$w0rd',

             DEFAULT_DATABASE = MASTER,

             DEFAULT_LANGUAGE = ENGLISH,

             CHECK_POLICY = OFF,

             CHECK_EXPIRATION = OFF;

       END

GO----------------------------------------------------------------------------------

USE AVISPA;

GO

IF NOT EXISTS(SELECT * FROM SYS.sysusers WHERE NAME = 'UserName')

       BEGIN

             CREATE USER UserNameFOR LOGIN UserName

       END

ELSE

       BEGIN

             ALTER USER UserNameWITH LOGIN = UserName

       END

 -- agrega el usuario al ROL

ALTER ROLE [ur_RoleName] ADD  MEMBER [UserName];

 ALTER ROLE [db_datareader]  ADD MEMBER [ur_RoleName]

 ALTER ROLE [db_datawriter]  ADD MEMBER [ur_RoleName]

 ALTER ROLE [db_execute]     ADD MEMBER [ur_RoleName]

------------------------------------------------------------------------------------------

       GRANT SELECT, UPDATE, INSERT, DELETE    ON [Schema].[Tabla] TO [UserName]   

       GRANT EXECUTE                           ON [Schema].[up_Procedure] TO [UserName]

       --     EXEC sp_addrolemember  N'db_datareader' ,UserName

       --     EXEC sp_addrolemember  N'db_datawriter' ,UserName

       --     EXEC sp_addrolemember  N'db_execute' ,UserName

       --     EXEC sp_addrolemember  N'db_ddladmin' ,UserName

-- FUNCIONES

GRANT SELECT ON [Brokerage].[fn_GetFees]                           TO [ur_RoleName];

-- ESCALAR FUNCTION

GRANT EXECUTE ON [Instrument].fn_GetMaturityDate                   TO [ur_RoleName];

-------------------------------------- select a todo el esquema 

GRANT SELECT ON SCHEMA :: Contact      TO [ur_RoleName];

GO

-- PARA QUE NO VEA LAS DEFINICIONES DE LA BASE DE DATOS
-----------------------------------------------------------------------------------

DENY VIEW ANY DEFINITION TO UserName

DENY EXECUTE ON SP_TABLES TO UserName

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

 -- PARA QUE PUEDAN CREAR Y MODIFICAR PROCEDURES Y VISTAS EN CIERTOS ESQUEMAS

 -----------------------------------------------------------------------------------

 GRANT CREATE PROCEDURE               TO [ur_RoleName]

 GRANT CREATE VIEW                    TO [ur_RoleName]

 -------- LOS PERMISOS DE MODIFICAR OBJETOS SON PARA LOS SIGUIENTES ESQUEMAS

 GRANT ALTER                      ON SCHEMA::Access          TO [ur_RoleName]

 GRANT ALTER                      ON SCHEMA::Brokerage       TO [ur_RoleName]

 GRANT ALTER                      ON SCHEMA::CAP             TO [ur_RoleName]

 GRANT ALTER                      ON SCHEMA::Client          TO [ur_RoleName]

-----------------------------------------------------------------------------------

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

-- crea el rol

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

if NOT exists (select name from sys.database_principals where name = 'db_execute')

  BEGIN

       CREATE ROLE [db_execute] AUTHORIZATION [dbo];

  END

  GO

  GRANT EXECUTE TO [db_execute]

 GO------------------------