sql-server - una - saber el tamaño de cada tabla sql server

Obtener el tamaño de todas las tablas en la base de datos

Question

He heredado una base de datos SQL Server bastante grande. Parece que ocupa más espacio del que podría esperar, dados los datos que contiene.

¿Hay una manera fácil de determinar cuánto espacio en disco consume cada tabla?

Answer 1

Agregué algunas columnas más sobre la respuesta de marc_s:

with fs
as
(
select i.object_id,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKb
from sys.indexes i INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
i.OBJECT_ID > 255
GROUP BY
i.object_id,
p.rows
)
SELECT
t.NAME AS TableName,
fs.RowCounts,
fs.TotalSpaceKb,
t.create_date,
t.modify_date,
( select COUNT(1)
from sys.columns c
where c.object_id = t.object_id ) TotalColumns
FROM
sys.tables t INNER JOIN
fs ON t.OBJECT_ID = fs.object_id
WHERE
t.NAME NOT LIKE ''dt%''
AND t.is_ms_shipped = 0
ORDER BY
t.Name

Answer 2

Aquí hay otro método: utilizando SQL Server Management Studio , en el Explorador de objetos , vaya a su base de datos y seleccione Tablas

Luego abra los Detalles del Explorador de objetos (ya sea presionando F7 o vaya a Ver-> Detalles del Explorador de objetos ). En la página de detalles del explorador de objetos, haga clic derecho en el encabezado de la columna y habilite las columnas que le gustaría ver en la página. También puede ordenar los datos por cualquier columna.

Answer 3

Como una extensión simple de la respuesta de marc_s (la que ha sido aceptada), esto se ajusta para devolver el recuento de columnas y permitir el filtrado:

SELECT *
FROM
(
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
COUNT(DISTINCT c.COLUMN_NAME) as ColumnCount,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
(SUM(a.used_pages) * 8) AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN
INFORMATION_SCHEMA.COLUMNS c ON t.NAME = c.TABLE_NAME
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ''dt%''
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
) AS Result
WHERE
RowCounts > 1000
AND ColumnCount > 10
ORDER BY
UsedSpaceKB DESC

Answer 4

Desde un símbolo del sistema utilizando OSQL :

OSQL -E -d <*databasename*> -Q "exec sp_msforeachtable ''sp_spaceused [?]''" > result.txt

Answer 5

Después de algunas búsquedas, no pude encontrar una manera fácil de obtener información sobre todas las tablas. Existe un práctico procedimiento almacenado llamado sp_spaceused que devolverá todo el espacio utilizado por la base de datos. Si se le proporciona un nombre de tabla, devuelve el espacio utilizado por esa tabla. Sin embargo, los resultados devueltos por el procedimiento almacenado no se pueden ordenar, ya que las columnas son valores de caracteres.

El siguiente script generará la información que estoy buscando.

create table #TableSize (
Name varchar(255),
[rows] int,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255))
create table #ConvertedSizes (
Name varchar(255),
[rows] int,
reservedKb int,
dataKb int,
reservedIndexSize int,
reservedUnused int)
EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused ''?''"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize
select * from #ConvertedSizes
order by reservedKb desc
drop table #TableSize
drop table #ConvertedSizes

Answer 6

Esta es una manera de obtener todos los tamaños de tablas rápidamente con los siguientes pasos:

1. Escriba los comandos T-SQL dados para listar todas las tablas de la base de datos:

   select ''exec sp_spaceused '' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE''

2. Ahora copie la lista de tablas de la base de datos y cópiela en una nueva ventana del analizador de consultas

   exec sp_spaceused table1
   exec sp_spaceused table2
   exec sp_spaceused table3
   exec sp_spaceused table4
   exec sp_spaceused table5

3. En el analizador de consultas SQL, seleccione de la barra de herramientas superior la opción Resultados a archivo ( Ctrl + Shift + F ).

4. Ahora, finalmente, pulse el botón Ejecutar rojo marcado desde la barra de herramientas anterior.

5. El tamaño de la base de datos de todas las tablas ahora se almacena en un archivo en su computadora.

Answer 7

Esto le dará los tamaños y el número de registros para cada tabla.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Get a list of tables and their sizes on disk
ALTER PROCEDURE [dbo].[sp_Table_Sizes]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)
INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = ''BASE TABLE''
DECLARE c1 CURSOR FOR
SELECT Table_Schema + ''.'' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = ''BASE TABLE''
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, ''['','''');
SET @table_name = REPLACE(@table_name, '']'','''');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_Table
END

Answer 8

La extensión a @xav answer que manejó particiones de tablas para obtener el tamaño en MB y GB. Probado en SQL Server 2008/2012 (comentó una línea donde is_memory_optimized = 1 )

SELECT
a2.name AS TableName,
a1.rows as [RowCount],
--(a1.reserved + ISNULL(a4.reserved,0)) * 8 AS ReservedSize_KB,
--a1.data * 8 AS DataSize_KB,
--(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS IndexSize_KB,
--(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS UnusedSize_KB,
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB,
--''| |'' Separator_MB_GB,
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_GB,
CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_GB,
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_GB,
CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_GB
FROM
(SELECT
ps.object_id,
SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
--===Remove the following comment for SQL Server 2014+
--WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N''S'' and a2.type <> N''IT''
--AND a2.name = ''MyTable'' --Filter for specific table
--ORDER BY a3.name, a2.name
ORDER BY ReservedSize_MB DESC

Answer 9

Las consultas anteriores son buenas para encontrar la cantidad de espacio utilizado por la tabla (incluidos los índices), pero si desea comparar la cantidad de espacio que utilizan los índices en la tabla, use esta consulta:

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS ''Indexsize(KB)''
FROM
sys.indexes AS i JOIN
sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id JOIN
sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY
i.OBJECT_ID,
i.index_id,
i.name
ORDER BY
OBJECT_NAME(i.OBJECT_ID),
i.index_id

Answer 10

Mi publicación solo es relevante para SQL Server 2000 y se ha probado para que funcione en mi entorno.

Este código accede a todas las bases de datos posibles de una sola instancia , no solo a una única base de datos.

Utilizo dos tablas temporales para ayudar a recopilar los datos apropiados y luego vuelco los resultados en una tabla ''En vivo''.

Los datos devueltos son: DatabaseName, DatabaseTableName, Rows (en la tabla), datos (el tamaño de la tabla en KB parece), datos de entrada (me parece útil para saber cuándo ejecuté el script por última vez).

El inconveniente de este código es que el campo ''datos'' no se almacena como un int (los caracteres ''KB'' se guardan en ese campo), y eso sería útil (pero no totalmente necesario) para la clasificación.

¡Ojalá este código ayude a alguien y ahorre algo de tiempo!

CREATE PROCEDURE [dbo].[usp_getAllDBTableSizes]
AS
BEGIN
SET NOCOUNT OFF
CREATE TABLE #DatabaseTables([dbname] sysname,TableName sysname)
CREATE TABLE #AllDatabaseTableSizes(Name sysname,[rows] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
DECLARE @SQL nvarchar(4000)
SET @SQL=''select ''''?'''' AS [Database], Table_Name from [?].information_schema.tables WHERE TABLE_TYPE = ''''BASE TABLE'''' ''
INSERT INTO #DatabaseTables(DbName, TableName)
EXECUTE sp_msforeachdb @Command1=@SQL
DECLARE AllDatabaseTables CURSOR LOCAL READ_ONLY FOR
SELECT TableName FROM #DatabaseTables
DECLARE AllDatabaseNames CURSOR LOCAL READ_ONLY FOR
SELECT DBName FROM #DatabaseTables
DECLARE @DBName sysname
OPEN AllDatabaseNames
DECLARE @TName sysname
OPEN AllDatabaseTables
WHILE 1=1 BEGIN
FETCH NEXT FROM AllDatabaseNames INTO @DBName
FETCH NEXT FROM AllDatabaseTables INTO @TName
IF @@FETCH_STATUS<>0 BREAK
INSERT INTO #AllDatabaseTableSizes
EXEC ( ''EXEC '' + @DBName + ''.dbo.sp_spaceused '' + @TName)
END
--http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspx
INSERT INTO rsp_DatabaseTableSizes (DatabaseName, name, [rows], data)
SELECT [dbname], name, [rows], data FROM #DatabaseTables
INNER JOIN #AllDatabaseTableSizes
ON #DatabaseTables.TableName = #AllDatabaseTableSizes.Name
GROUP BY [dbname] , name, [rows], data
ORDER BY [dbname]
--To be honest, I have no idea what exact duplicates we are dropping
-- but in my case a near enough approach has been good enough.
DELETE FROM [rsp_DatabaseTableSizes]
WHERE name IN
(
SELECT name
FROM [rsp_DatabaseTableSizes]
GROUP BY name
HAVING COUNT(*) > 1
)
DROP TABLE #DatabaseTables
DROP TABLE #AllDatabaseTableSizes
CLOSE AllDatabaseTables
DEALLOCATE AllDatabaseTables
CLOSE AllDatabaseNames
DEALLOCATE AllDatabaseNames
END
--EXEC [dbo].[usp_getAllDBTableSizes]

En caso de que necesite saberlo, la tabla rsp_DatabaseTableSizes se creó a través de:

CREATE TABLE [dbo].[rsp_DatabaseSizes](
[DatabaseName] [varchar](1000) NULL,
[dbSize] [decimal](15, 2) NULL,
[DateUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO

Answer 11

Para obtener todo el tamaño de la tabla en una base de datos, puede utilizar esta consulta:

Exec sys.sp_MSforeachtable '' sp_spaceused "?" ''

Y puede cambiarlo para insertar todos los resultados en la tabla temporal y luego seleccionar la tabla temporal.

Insert into #TempTable Exec sys.sp_MSforeachtable '' sp_spaceused "?" ''
Select * from #TempTable

Answer 12

Riffing en @Mark respuesta anterior, agregó @ updateusage = ''true'' para forzar las estadísticas de tamaño más recientes ( https://msdn.microsoft.com/en-us/library/ms188776.aspx ):

 SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = ''exec sp_spaceused @objname =''''?'''', @updateusage =''''true'''' ''
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,'' KB'','''')) DESC

Answer 13

Si está utilizando SQL Server Management Studio (SSMS), en lugar de ejecutar una consulta ( que en mi caso devolvió filas duplicadas ) puede ejecutar un informe estándar .

1. Haga clic derecho en la base de datos
2. Vaya a Informes> Informes estándar> Uso del disco por tabla

Nota: El nivel de compatibilidad de la base de datos debe establecerse en 90 o superior para que esto funcione correctamente. Consulte http://msdn.microsoft.com/en-gb/library/bb510680.aspx

Answer 14

Si necesita calcular exactamente los mismos números, que están en la página de ''propiedades de tabla - almacenamiento'' en SSMS, debe contarlos con el mismo método que se hizo en SSMS (funciona para el servidor SQL 2005 y superior ... y también funciona correctamente para tablas con campos LOB, porque el simple hecho de contar "used_pages" no es suficiente para mostrar el tamaño del índice exacto):

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END) as pages
FROM sys.dm_db_partition_stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
cte.TableName,
cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB,
cast(((CASE WHEN cte.used_pages_count > cte.pages
THEN cte.used_pages_count - cte.pages
ELSE 0
END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

Answer 15

Un pequeño cambio en la respuesta de Mar_c, ya que he vuelto a esta página con tanta frecuencia, ordenada por la mayoría de las primeras filas:

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ''dt%''
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
--p.rows DESC --Uncomment to order by amount rows instead of size in KB.
SUM(a.total_pages) DESC

Answer 16

Utilizamos la partición de tablas y tuvimos algunos problemas con las consultas proporcionadas anteriormente debido a registros duplicados.

Para aquellos que lo necesitan, puede encontrar debajo de la consulta ejecutada por SQL Server 2014 al generar el informe "Uso de disco por tabla". Supongo que también funciona con versiones anteriores de SQL Server.

Funciona a las mil maravillas.

SELECT
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N''S'' and a2.type <> N''IT''
ORDER BY a3.name, a2.name

Answer 17

sp_spaceused puede obtener información sobre el espacio en disco utilizado por una tabla, vista indizada o la base de datos completa.

Por ejemplo:

USE MyDatabase; GO
EXEC sp_spaceused N''User.ContactInfo''; GO

Esto informa la información de uso del disco para la tabla ContactInfo.

Para usar esto para todas las tablas a la vez:

USE MyDatabase; GO
sp_msforeachtable ''EXEC sp_spaceused [?]'' GO

También puede obtener el uso del disco desde la funcionalidad de Informes estándar de SQL Server. Para acceder a este informe, navegue desde el objeto del servidor en el Explorador de objetos, desplácese hasta el objeto Bases de datos y luego haga clic con el botón derecho en cualquier base de datos. En el menú que aparece, seleccione Informes, luego Informes estándar y luego "Uso del disco por partición: [Nombre de la base de datos]".

Answer 18

 exec sp_spaceused N''dbo.MyTable''

Para todas las tablas, use .. (agregando de los comentarios de Paul)

exec sp_MSForEachTable ''exec sp_spaceused [?]''

Answer 19

-- Show the size of all the tables in a database sort by data size descending
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = ''exec sp_spaceused ''''?''''''
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,'' KB'','''')) DESC

Answer 20

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ''dt%''
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name