{"id":66,"date":"2020-02-02T18:19:06","date_gmt":"2020-02-02T18:19:06","guid":{"rendered":"http:\/\/ictbank.ir\/blog\/?p=66"},"modified":"2020-03-26T00:52:33","modified_gmt":"2020-03-26T00:52:33","slug":"rebuild-and-reorganize-all-indexes-within-a-database","status":"publish","type":"post","link":"https:\/\/ictbank.ir\/blog\/rebuild-and-reorganize-all-indexes-within-a-database\/","title":{"rendered":"Rebuild and Reorganize all indexes within a database"},"content":{"rendered":"\n

If you want to REBUILD<\/strong> and REORGANIZE <\/strong>all your indexes in a database, you have to use this query:<\/p>\n\n\n\n

Rebuild:<\/p>\n\n\n

\nDECLARE @Loop int\nDECLARE @Loop2 int\nDECLARE @MaxLoop int\nDECLARE @MaxLoop2 int\nDECLARE @DBName varchar(300)\nDECLARE @SQL varchar(max)\n\nSET @Loop = 1\nSET @DBName = ''\n\nset nocount on\nSET @MaxLoop =  (select count([name]) FROM sys.databases where [name] like '%')\nWHILE @Loop &lt;= @MaxLoop\n    BEGIN\n        SET @DBName = (select TableWithRowsNumbers.name from (select ROW_NUMBER() OVER (ORDER by [name]) as Row,[name] FROM sys.databases where [name] like 'Z%' ) TableWithRowsNumbers where Row = @Loop)\n        SET @SQL = 'USE [' + @DBName + ']'\n        print (@SQL)\n\t\texec (@SQL)\n\t\t\n\t\tSET @Loop2 = 1\n\n\t\tSET @MaxLoop2 =  (\n\t\tSELECT \n\t\t\tCount(indexstats.database_id) \n\t\t\tFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats\n\t\t\tINNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]\n\t\t\tINNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]\n\t\t\tINNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]\n\t\t\tAND indexstats.index_id = dbindexes.index_id\n\t\t\tWHERE indexstats.database_id = DB_ID()\n\t\t);\n\t\tWHILE @Loop2 &lt;= @MaxLoop2\n\t\t\tbegin\n\t\t\tset @SQL = \n\t\t\t(\n\t\t\t\tselect xx.cmd from \n\t\t\t\t(\n\t\t\t\t\tSELECT \n\t\t\t\t\tROW_NUMBER() OVER (ORDER by indexstats.avg_fragmentation_in_percent) as Row,\n\t\t\t\t\tindexstats.database_id ,\n\t\t\t\t\tdbschemas.[name] as 'Schema',\n\t\t\t\t\tdbtables.[name] as 'Table',\n\t\t\t\t\tdbindexes.[name] as 'Index',\n\t\t\t\t\tindexstats.avg_fragmentation_in_percent,\n\t\t\t\t\tindexstats.page_count,\n\t\t\t\t\t'alter index ' +  dbindexes.[name] + ' on ' + dbschemas.[name] + '.' + dbtables.[name] + ' rebuild;' as cmd\n\t\t\t\t\tFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats\n\t\t\t\t\tINNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]\n\t\t\t\t\tINNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]\n\t\t\t\t\tINNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]\n\t\t\t\t\tAND indexstats.index_id = dbindexes.index_id\n\t\t\t\t\tWHERE indexstats.database_id = DB_ID()\n\t\t\t\t) xx where Row = @Loop2\n\t\t\t)\n\t\t\texec (@SQL)\n\t\t\tprint (@SQL)\n\t\t\t\n\t\t\tset @Loop2 = @Loop2+1;\n\t\tend\n        set @Loop = @Loop + 1;\n    END\n<\/pre><\/div>\n\n\n
Reorganize:<\/p>\n\n\n
\nDECLARE @Loop int\nDECLARE @Loop2 int\nDECLARE @MaxLoop int\nDECLARE @MaxLoop2 int\nDECLARE @DBName varchar(300)\nDECLARE @SQL varchar(max)\n\nSET @Loop = 1\nSET @DBName = ''\n\nset nocount on\nSET @MaxLoop =  (select count([name]) FROM sys.databases where [name] like '%')\nWHILE @Loop &lt;= @MaxLoop\n    BEGIN\n        SET @DBName = (select TableWithRowsNumbers.name from (select ROW_NUMBER() OVER (ORDER by [name]) as Row,[name] FROM sys.databases where [name] like 'Z%' ) TableWithRowsNumbers where Row = @Loop)\n        SET @SQL = 'USE [' + @DBName + ']'\n        print (@SQL)\n\t\texec (@SQL)\n\t\t\n\t\tSET @Loop2 = 1\n\n\t\tSET @MaxLoop2 =  (\n\t\tSELECT \n\t\t\tCount(indexstats.database_id) \n\t\t\tFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats\n\t\t\tINNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]\n\t\t\tINNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]\n\t\t\tINNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]\n\t\t\tAND indexstats.index_id = dbindexes.index_id\n\t\t\tWHERE indexstats.database_id = DB_ID()\n\t\t);\n\t\tWHILE @Loop2 &lt;= @MaxLoop2\n\t\t\tbegin\n\t\t\tset @SQL = \n\t\t\t(\n\t\t\t\tselect xx.cmd from \n\t\t\t\t(\n\t\t\t\t\tSELECT \n\t\t\t\t\tROW_NUMBER() OVER (ORDER by indexstats.avg_fragmentation_in_percent) as Row,\n\t\t\t\t\tindexstats.database_id ,\n\t\t\t\t\tdbschemas.[name] as 'Schema',\n\t\t\t\t\tdbtables.[name] as 'Table',\n\t\t\t\t\tdbindexes.[name] as 'Index',\n\t\t\t\t\tindexstats.avg_fragmentation_in_percent,\n\t\t\t\t\tindexstats.page_count,\n\t\t\t\t\t'alter index ' +  dbindexes.[name] + ' on ' + dbschemas.[name] + '.' + dbtables.[name] + ' reorganize;' as cmd\n\t\t\t\t\tFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats\n\t\t\t\t\tINNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]\n\t\t\t\t\tINNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]\n\t\t\t\t\tINNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]\n\t\t\t\t\tAND indexstats.index_id = dbindexes.index_id\n\t\t\t\t\tWHERE indexstats.database_id = DB_ID()\n\t\t\t\t) xx where Row = @Loop2\n\t\t\t)\n\t\t\texec (@SQL)\n\t\t\tprint (@SQL)\n\t\t\t\n\t\t\tset @Loop2 = @Loop2+1;\n\t\tend\n        set @Loop = @Loop + 1;\n    END\n\n<\/pre><\/div> ","protected":false},"excerpt":{"rendered":"
If you want to REBUILD and REORGANIZE all your indexes in a database, you have to use this query: Rebuild: Reorganize:<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"templates\/template-full-width.php","format":"standard","meta":{"footnotes":""},"categories":[8,29,21],"tags":[6,30,24,31,32,33,23,25],"class_list":["post-66","post","type-post","status-publish","format-standard","hentry","category-database","category-indexes","category-mssql","tag-database","tag-index","tag-mssql","tag-rebuild","tag-reorganize","tag-script","tag-sql","tag-sqlserver"],"_links":{"self":[{"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/posts\/66","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/comments?post=66"}],"version-history":[{"count":3,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/posts\/66\/revisions"}],"predecessor-version":[{"id":139,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/posts\/66\/revisions\/139"}],"wp:attachment":[{"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/media?parent=66"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/categories?post=66"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ictbank.ir\/blog\/wp-json\/wp\/v2\/tags?post=66"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}