[MSSQL] - Cantidad de registros por tabla y tamaño utilizado en BDD

Hola muchachos,

De nuevo una entrada al blog de memorias de un desarrollador de software, esta semana traigo una consulta que nos es muy útil al momento de hacer seguimiento de las tablas en nuestra base de datos, y necesitamos por alguna u otra razón saber el tamaño de las tablas, cantidad de registros y fecha de creación de las mismas.

USE [BDD]

GO

/****** Object:  StoredProcedure [dbo].[PA_CONSULTA_SIZE_TABLE]    Script Date: 01/12/2016 14:32:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: segansoft@gmail.com

-- Create date: 20160110

-- Description: Consulta el size table de la base de datos

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

CREATE PROCEDURE [dbo].[PA_CONSULTA_SIZE_TABLE]

AS

BEGIN

                        -- SET NOCOUNT ON added to prevent extra result sets from

                        -- interfering with SELECT statements.

                        SET NOCOUNT ON;

    -- Insert statements for procedure here

                                               SELECT

                                                                       x.ID, X.[name], B.[crdate],

                                                                       REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],

                                                                       REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],

                                                                       REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],

                                                                       REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],

                                                                       REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]

                                               FROM (

                                                                       SELECT

                                                                                              A.ID,

                                                                                              CAST(object_name(A.id) AS varchar(50)) AS [name],

                                                                                              SUM(CASE WHEN A.indid < 2 THEN CONVERT(bigint, A.[rows]) END) AS [rows],

                                                                                              SUM(CONVERT(bigint, A.reserved)) * 8 AS reserved,

                                                                                              SUM(CONVERT(bigint, A.dpages)) * 8 AS data,

                                                                                              SUM(CONVERT(bigint, A.used) - CONVERT(bigint, A.dpages)) * 8 AS index_size,

                                                                                              SUM(CONVERT(bigint, A.reserved) - CONVERT(bigint, A.used)) * 8 AS unused

                                                                       FROM sysindexes as A WITH (NOLOCK)

                                                                       WHERE A.indid IN (0, 1, 255)

                                                                                                                      AND A.id > 100

                                                                                                                      AND object_name(A.id) <> 'dtproperties'

                                                                       GROUP BY A.id WITH ROLLUP

                                               ) AS X

                                               INNER JOIN sysobjects as B ON X.id = B.id

                                               WHERE X.[name] is not null

                                               ORDER BY CONVERT(bigint, X.[rows]) DESC

END

Este procedimiento almacenado lo he probado en instancias desde SQLSERVER 2000 y me es funcional en todas las versiones de MSSQL.

Especial atención al momento de utilizarlo, en la primera linea del store procedure se menciona la BDD en la cual debe registrarse, en este caso cambiaran la palabra BDD por el nombre de la base de datos y procedan a registrarla en su Base De Datos. También deben estar seguros que tienen permisos de DBO en la instancia en la que estan ejecutando este procedimiento almacenado.

Para los menos expertos en la materia, para ejecutar la consulta solo deben ejecutar la siguiente sentencia: EXEC [PA_CONSULTA_SIZE_TABLE]