{"id":1753,"date":"2016-10-19T10:59:16","date_gmt":"2016-10-19T15:59:16","guid":{"rendered":"https:\/\/www.bhinfo.com.mx\/cursos\/?p=1753"},"modified":"2024-01-11T09:44:59","modified_gmt":"2024-01-11T15:44:59","slug":"sql-reindex-basedata","status":"publish","type":"post","link":"https:\/\/www.bhinfo.com.mx\/cursos\/2016\/10\/19\/sql-reindex-basedata\/","title":{"rendered":"SQL, Reindex BaseData"},"content":{"rendered":"<blockquote><p><strong>C\u00f3digo para reindexar todas las tablas de una base de datos<\/strong><\/p><\/blockquote>\n<p><!--more--><\/p>\n<pre class=\"lang:tsql decode:true\">--\r\nDECLARE @TableName varchar(200)\r\nDECLARE TableCursor CURSOR FOR\r\nSELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES\r\nWHERE TABLE_TYPE = 'BASE TABLE'\r\nOPEN TableCursor\r\nFETCH NEXT FROM TableCursor INTO @TableName\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\n\r\nPRINT 'Reindexando ' + @TableName\r\nDBCC DBREINDEX (@TableName)\r\n\r\nFETCH NEXT FROM TableCursor INTO @TableName\r\nEND\r\n\r\nCLOSE TableCursor\r\nDEALLOCATE TableCursor\r\n--<\/pre>\n<p>&nbsp;<\/p>\n<p>ADDENDUM<br \/>\n<!--more--><\/p>\n<pre class=\"lang:tsql decode:true\">--\r\nDECLARE @TableName VARCHAR(255)\r\nDECLARE @sql NVARCHAR(500)\r\nDECLARE @fillfactor INT\r\nSET @fillfactor = 80 \r\nDECLARE TableCursor CURSOR FOR\r\nSELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName\r\nFROM sys.tables\r\nOPEN TableCursor\r\nFETCH NEXT FROM TableCursor INTO @TableName\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\nSET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'\r\nPRINT @sql\r\nEXEC (@sql)\r\nFETCH NEXT FROM TableCursor INTO @TableName\r\nEND\r\nCLOSE TableCursor\r\nDEALLOCATE TableCursor\r\nGO\r\n--<\/pre>\n<p>Crear Store Procedure (SP) para:<\/p>\n<p>1.- De fragmentar Indices<\/p>\n<p>2.- Reconstruir Indices<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:tsql decode:true \">--Exec xOptimiza 0\r\nCreate Proc xOptimiza\r\n@mov int\r\nAS\r\n-- Declaraci\u00f3n de variables necesarias para los procesos\r\nDECLARE @ID int, @Name varChar(128), @Reindexa int, @Indice varchar(128), @DB VarChar(128)\r\n\r\n\/*\r\nLos posibles valores que pude tomar la variable @Reindexa:\r\n\r\n\t0\t:\tMuestra la Fragmentacion de la informaci\u00f3n e indices\r\n\t1\t:\tDefragmenta los indices de las tablas\r\n\t2\t:\tMuestra la fragmentaci\u00f3n de la informacion e indices,\r\n\t\t\ty despues reconstrulle los indices de las tablas\r\n\t\t\tcambiandoles el fill-factor a 80 para un mejor desempe\u00f1o\r\n\t3\t:\tRevisa la integridad de la informaci\u00f3n, indices y la\r\n\t\t\timagen de la p\u00e1gina\r\n\t4\t:\tInforma el espacio utilizado por las tablas\r\n\t5\t:\tRevisa la consistenacia de el almacenamiento de la informacion en el \r\n\t\t\tdisco duro\r\n*\/\r\n-- Select db_name()\r\n-- Asigana un valor correcto a esta variable para iniciar el proceso deseado\r\nSelect \t@Reindexa = @mov,\r\n\t@DB = db_name()\r\nIf @Reindexa &lt; 5\r\nBegin\r\n-- Se obtiene la lista de las tablas de usuario para despues poderlas analizar\r\nDeclare Tablas Cursor For\r\n\t--Select id, rTrim(name) from sysobjects where xtype = 'U' Order by name\r\n        --Se mejoro metodo de enlistado de tablas al agregar su esquema correspondiente\r\n          Select id, rTrim(QUOTENAME(OBJECT_SCHEMA_NAME([id]))+'.' + QUOTENAME(name)) from sysobjects where xtype = 'U' Order by name\r\n-- Abre el cursor \"TABLAS\" para recorrer tabla por tabla\r\nOpen Tablas\r\n\t-- Obtiene el primer registro de las tablas\r\n\tFetch Next From Tablas\r\n\t\tInto @ID, @Name\r\n\tWhile @@Fetch_Status = 0\r\n\tBegin\r\n\t\tPrint '******************************************************************* ' + @Name\r\n\t\t\r\n\t\tIF @Reindexa = 0\r\n\t\t-- Muestra la Fragmentacion de la informaci\u00f3n e indices\r\n\t\tDbcc Showcontig (@ID)\r\n\r\n\t\tIF @Reindexa = 1\r\n\t\tBegin\r\n\t\t-- Defragmenta los indices de una tabla\r\n\t\t\tIf Exists (Select Name From sysindexes Where Id = @ID and IndId &lt;&gt; 0) \r\n\t\t\tBegin\r\n\t\t\t\tDeclare Indices Cursor For\r\n\t\t\t\t\tSelect name From sysindexes Where id = @ID and indid &lt;&gt; 0 and rows &lt;&gt; 0\r\n\t\t\t\tOpen Indices\r\n\t\t\t\t\tFetch Next From Indices\r\n\t\t\t\t\t\tInto @Indice\r\n\t\t\t\t\tWhile @@Fetch_Status = 0\r\n\t\t\t\t\tBegin\r\n\t\t\t\t\t\tPrint '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ' + @Indice\r\n\t\t\t\t\t\tDBCC INDEXDEFRAG (@DB,@Name, @Indice)\r\n\t\t\t\t\t\t-- Select @Indice, @Name\r\n\t\t\t\t\t\tFetch Next From Indices\r\n\t\t\t\t\t\t\tInto @Indice\r\n\t\t\t\t\tEnd\r\n\t\t\t\tClose Indices\r\n\t\t\t\tDeallocate Indices\r\n\t\t\tEnd\r\n\t\t\tElse\r\n\t\t\t\tprint '-- No tiene indices que defragmentar'\r\n\r\n\t\tEnd\r\n\t\tIF @Reindexa = 2\r\n\t\tBegin\r\n\t\t\t-- Muestra la fragmentaci\u00f3n de la informacion e indices, y despues\r\n\t\t\t-- reconstrulle los indices de la tabla cambiandoles el fill-factor a 60 para un mejor desempe\u00f1o\r\n\t\t\tIf Exists (Select name From sysindexes Where id = @ID and indid &lt;&gt; 0) \r\n\t\t\tBegin\r\n\t\t\t\tDbcc Showcontig (@ID)\r\n\t\t\t\tDeclare Indices Cursor For\r\n\t\t\t\t\tSelect name From sysindexes Where id = @ID and indid &lt;&gt; 0 and rows &lt;&gt; 0\r\n\t\t\t\tOpen Indices\r\n\t\t\t\t\tFetch Next From Indices\r\n\t\t\t\t\t\tInto @Indice\r\n\t\t\t\t\tWhile @@Fetch_Status = 0\r\n\t\t\t\t\tBegin\r\n\t\t\t\t\t\tPrint '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ' + @Indice\r\n\t\t\t\t\t\tDBCC DBREINDEX (@Name, @Indice, 80)\r\n\t\t\t\t\t\t-- Select @Indice, @Name\r\n\t\t\t\t\t\tFetch Next From Indices\r\n\t\t\t\t\t\t\tInto @Indice\r\n\t\t\t\t\tEnd\r\n\t\t\t\tClose Indices\r\n\t\t\t\tDeallocate Indices\r\n\t\t\tEnd\r\n\t\t\tElse\r\n\t\t\t\tprint '-- No tiene indices que defragmentar'\r\n\t\tEnd\r\n\r\n\t\tIF @Reindexa = 3\r\n\t\t-- Revisa la integridad de la informaci\u00f3n, indices y la imagen de la p\u00e1gina\r\n\t\tDBCC CheckTable (@Name)\r\n\r\n\t\tIF @Reindexa = 4\r\n\t\t-- Informa el espacio utilizado por las tablas\r\n\t\tEXEC sp_spaceused @Name\r\n\r\n\t\tPrint ''\r\n\t\tPrint ''\r\n\t\t-- Obtiene siguiente registro de tablas\r\n\t\tFetch Next From Tablas\r\n\t\t\tInto @ID, @Name\r\n\tEnd\r\n-- Cierra y libera el cursor \"TABLAS\"\r\nClose Tablas\r\nDeallocate Tablas\r\nEnd\r\n\r\nIF @Reindexa = 5\r\n-- Revisa la consistenacia de el almacenamiento de la informacion en el \r\n-- disco duro\r\nDBCC CHECKALLOC (@DB)\r\n\r\n\r\nIF @Reindexa = 6\r\nDBCC CHECKALLOC (@DB, REPAIR_REBUILD)\r\n\r\n-- Reactiva la opcion para contar los registros\r\n--Set NoCount OFF<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>C\u00f3digo para reindexar todas las tablas de una base de datos<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[70,76,193],"tags":[194,229,228,53,230],"class_list":["post-1753","post","type-post","status-publish","format-standard","hentry","category-ddavila","category-como-hacer","category-sql","tag-basedata","tag-indexar","tag-indices","tag-sql","tag-table"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/posts\/1753","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/comments?post=1753"}],"version-history":[{"count":7,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/posts\/1753\/revisions"}],"predecessor-version":[{"id":11103,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/posts\/1753\/revisions\/11103"}],"wp:attachment":[{"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/media?parent=1753"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/categories?post=1753"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/tags?post=1753"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}