Consulta SQL. AYUDA

[iislas]

Espero que esto sea lo que buscas

Código SQL:

/*----------------------------------------------------------------------
-- Rutina para edicion 2005 y superior
--
*/----------------------------------------------------------------------
SET NOCOUNT ON;  
DECLARE  
    @fiMaxFilas INT,  
    @fiRenglonActual INT,  
    @fcComando VARCHAR(120)  
SET @fiRenglonActual = 1  
     
DECLARE @AllTables TABLE  
(  
    fiIndice INT NOT NULL PRIMARY KEY,  
    fcNombreTabla VARCHAR(100) NOT NULL  
)  
  
INSERT INTO @AllTables  
SELECT ROW_NUMBER() OVER(ORDER BY name) AS fiIndice, name  
FROM sysobjects  
WHERE TYPE = 'U'  
  
SELECT @fiMaxFilas = MAX(fiIndice) FROM @AllTables  
WHILE (@fiRenglonActual <= @fiMaxFilas )  
BEGIN  
    SELECT @fcComando = 'sp_spaceused '+ fcNombreTabla FROM @AllTables WHERE fiIndice = @fiRenglonActual     
    EXECUTE (@fcComando)  
    SET @fiRenglonActual = @fiRenglonActual+1  
END  
 
 
/*----------------------------------------------------------------------
-- Rutina para edicion 2000 y superior
--
*/----------------------------------------------------------------------
SET NOCOUNT ON;  
DROP TABLE #Resultado
DECLARE  
    @fiMaxFilas INT,  
    @fiRenglonActual INT,  
    @fcComando VARCHAR(120)  
     
    SET @fiRenglonActual = 1  
     
DECLARE @AllTables TABLE  
(  
    fiIndice INT NOT NULL PRIMARY KEY IDENTITY(1,1),  
    fcNombreTabla VARCHAR(100) NOT NULL  
)  
  
INSERT INTO @AllTables  
SELECT -- ROW_NUMBER() OVER(ORDER BY name) AS fiIndice,  
name  
FROM sysobjects  
WHERE TYPE = 'U'  
  
SELECT @fiMaxFilas = MAX(fiIndice) FROM @AllTables  
CREATE TABLE #Resultado (Name sysname, ROWS VARCHAR(50), Reserved VARCHAR(50), DATA VARCHAR(50), Index_size VARCHAR(50), Unused VARCHAR(50))
  
WHILE (@fiRenglonActual <= @fiMaxFilas )  
BEGIN  
    SELECT @fcComando = 'sp_spaceused '+ fcNombreTabla FROM @AllTables WHERE fiIndice = @fiRenglonActual 
    INSERT INTO #Resultado
    EXECUTE (@fcComando)  
    SET @fiRenglonActual = @fiRenglonActual+1  
END  
 
 
SELECT Name, 
ROWS, 
CAST(REPLACE(Reserved, 'KB', '') AS INT) AS Reserved,
CAST(REPLACE(DATA, 'KB', '') AS INT) AS DATA,
CAST(REPLACE(Index_size, 'KB', '') AS INT) AS Index_size,
CAST(REPLACE(Unused, 'KB', '') AS INT) AS Unused
FROM #Resultado
ORDER BY 3 DESC