Technical Article

Script to Obtain Fragmentation in all databases.

,

This script obtains the fragmentation in all databases on a SQL Server 2000 database server.

You must firt create a table(Fragmentacion) on a database (Sistemas) to store the information obtained.

if exists (select name from sysobjects where name = 'sp_CalcularFragmentacion' and type = 'p' )
    drop proc sp_CalcularFragmentacion
go

/*El siguiente script calcula la fragmentación de todas las tablas de las bases de datos de un servidor
de SQL Server 2000. Esta información se almacenará en una tabla.*/

create proc sp_CalcularFragmentacion @dbname varchar(128) = null WITH ENCRYPTION 
as

-- Comprobarción de que la base de datos selecionada existe

if @dbname is not null and @dbname not in (select name from sysdatabases)
		begin
			raiserror('relax!... tas pasao, de veras tio, esa db no la tenemos, ala, intentalo otra vez ', 16, 1)
			return (1)
		end

set nocount on

-- Comprobación de que la tabla temporal que se va a utilizar no existe

if exists (select * from sysobjects where name = '#Frag' and type = 'u')
		drop table #Frag


CREATE TABLE #Frag (
   Nombre_BBDD VARCHAR (128),
   Nombre_objeto CHAR (255),
   Id_Objeto INT,
   Nombre_Indice CHAR (255),
   Id_Indice INT,
   Nivel_Indice INT,
   Total_Paginas INT,
   Total_Filas INT,
   TamRegMin INT,
   TamRegMax INT,
   TamRegMed INT,
   ForRecCount INT,
   Extents INT,
   Extents_Saltos INT,
   BytesLibresMed INT,
   DensidadPagMed INT,
   DensidadScan DECIMAL,
   BestCount INT,
   ActualCount INT,
   Fragmentacion_Logica DECIMAL,
   Fragmentacion_Extent DECIMAL,
   Fecha Datetime
)
declare @fecha datetime
set @fecha = (Select convert(varchar(16),getdate(),120))

if @dbname is null
		declare dbname cursor for select name from sysdatabases where status NOT IN (32,64,128,256,512,1024,1073747456) order by name asc
else if @dbname is not null
begin
		declare dbname cursor for select name from sysdatabases where name = @dbname	
end
open dbname
fetch next from dbname into @dbname
while @@fetch_status = 0
	begin	

		
EXEC ('USE '+@dbname+ '
		
	
		-- Declare variables
		SET NOCOUNT ON
		DECLARE @tablename varchar(128)
		DECLARE @bbdd VARCHAR (128)
		
		-- Declare cursor
		DECLARE tables CURSOR FOR
		   SELECT TABLE_NAME
		   FROM INFORMATION_SCHEMA.TABLES
		   WHERE TABLE_TYPE = ''BASE TABLE''

		

		-- Open the cursor
		OPEN tables

		-- Recorrido de las tablas de la base de datos
		FETCH NEXT FROM tables INTO @tablename
		
			WHILE @@FETCH_STATUS  = 0
			BEGIN
			-- Realización  del Showcontig
 				--Set @BBDD ='+@dbname+'
				INSERT INTO #Frag (Nombre_objeto,Id_Objeto,Nombre_Indice,Id_Indice,Nivel_Indice,Total_Paginas,Total_Filas,TamRegMin,TamRegMax,TamRegMed,ForRecCount,Extents,Extents_Saltos,BytesLibresMed,DensidadPagMed,DensidadScan,BestCount,ActualCount,Fragmentacion_Logica,Fragmentacion_Extent)
 		 		EXEC (''DBCC SHOWCONTIG (''+ @Tablename + '') 
  		 		   WITH TABLERESULTS, NO_INFOMSGS'')
				UPDATE #Frag SET Nombre_BBDD='''+@dbname+'''
				UPDATE #Frag SET Fecha='''+@fecha+'''
				INSERT INTO Sistemas..Fragmentacion (Nombre_BBDD,Nombre_Objeto,Id_Objeto,Total_Paginas,Total_Filas,Extents,Extents_Saltos,BestCount,ActualCount,Fragmentacion_Logica,Fragmentacion_Extent, Fecha ) exec (''Select Nombre_BBDD,Nombre_Objeto,Id_Objeto,Total_Paginas,Total_Filas,Extents,Extents_Saltos,BestCount,ActualCount,Fragmentacion_Logica,Fragmentacion_Extent, Fecha from #Frag'')
				Truncate table #Frag
				FETCH NEXT FROM tables INTO @tablename 
			END

			CLOSE tables
			DEALLOCATE tables
	')		

fetch next from dbname into @dbname
end


close dbname
deallocate dbname

drop table #Frag




/* script para crear la tabla de almacenamiento */

CREATE TABLE [Fragmentacion] (
	[Nombre_BBDD] [varchar] (128) ,
	[Nombre_objeto] [char] (255) ,
	[Id_Objeto] [int] NULL ,
	[Total_Paginas] [int] NULL ,
	[Total_Filas] [int] NULL ,
	[Extents] [int] NULL ,
	[Extents_Saltos] [int] NULL ,
	[BestCount] [int] NULL ,
	[ActualCount] [int] NULL ,
	[Fragmentacion_Logica] [decimal](18, 0) NULL ,
	[Fragmentacion_Extent] [decimal](18, 0) NULL ,
	[Fecha] [datetime] NULL 
) ON [PRIMARY]
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating