6xVHdBun

1Get size of all tables in database
2SELECT 
3    t.NAME AS TableName,
4    p.rows AS RowCounts,
5    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
6    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
7    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
8FROM 
9    sys.tables t
10INNER JOIN      
11    sys.indexes i ON t.OBJECT_ID = i.object_id
12INNER JOIN 
13    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
14INNER JOIN 
15    sys.allocation_units a ON p.partition_id = a.container_id
16WHERE 
17    t.NAME NOT LIKE 'dt%' 
18    AND t.is_ms_shipped = 0
19    AND i.OBJECT_ID > 255 
20GROUP BY 
21    t.Name, p.Rows
22ORDER BY 
23    t.Name
24	
25exec  sp_spaceused N'dbo.MyTable'
26	
27create table #TableSize (
28    Name varchar(255),
29    [rows] int,
30    reserved varchar(255),
31    data varchar(255),
32    index_size varchar(255),
33    unused varchar(255))
34create table #ConvertedSizes (
35    Name varchar(255),
36    [rows] int,
37    reservedKb int,
38    dataKb int,
39    reservedIndexSize int,
40    reservedUnused int)
41
42EXEC sp_MSforeachtable @command1="insert into #TableSize
43EXEC sp_spaceused '?'"
44insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
45select name, [rows], 
46SUBSTRING(reserved, 0, LEN(reserved)-2), 
47SUBSTRING(data, 0, LEN(data)-2), 
48SUBSTRING(index_size, 0, LEN(index_size)-2), 
49SUBSTRING(unused, 0, LEN(unused)-2)
50from #TableSize
51
52select * from #ConvertedSizes
53order by reservedKb desc
54
55drop table #TableSize
56drop table #ConvertedSizes
57	
58set ANSI_NULLS ON
59set QUOTED_IDENTIFIER ON
60GO
61-- Get a list of tables and their sizes on disk
62ALTER PROCEDURE [dbo].[sp_Table_Sizes]
63AS
64BEGIN
65    -- SET NOCOUNT ON added to prevent extra result sets from
66    -- interfering with SELECT statements.
67    SET NOCOUNT ON;
68DECLARE @table_name VARCHAR(500)  
69DECLARE @schema_name VARCHAR(500)  
70DECLARE @tab1 TABLE( 
71        tablename VARCHAR (500) collate database_default 
72       ,schemaname VARCHAR(500) collate database_default 
73) 
74
75CREATE TABLE #temp_Table ( 
76        tablename sysname 
77       ,row_count INT 
78       ,reserved VARCHAR(50) collate database_default 
79       ,data VARCHAR(50) collate database_default 
80       ,index_size VARCHAR(50) collate database_default 
81       ,unused VARCHAR(50) collate database_default  
82) 
83
84INSERT INTO @tab1  
85SELECT Table_Name, Table_Schema  
86FROM information_schema.tables  
87WHERE TABLE_TYPE = 'BASE TABLE' 
88
89DECLARE c1 CURSOR FOR 
90SELECT Table_Schema + '.' + Table_Name   
91FROM information_schema.tables t1  
92WHERE TABLE_TYPE = 'BASE TABLE' 
93
94OPEN c1 
95FETCH NEXT FROM c1 INTO @table_name 
96WHILE @@FETCH_STATUS = 0  
97BEGIN   
98        SET @table_name = REPLACE(@table_name, '[','');  
99        SET @table_name = REPLACE(@table_name, ']','');  
100
101        -- make sure the object exists before calling sp_spacedused 
102        IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name)) 
103        BEGIN 
104               INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false; 
105        END 
106
107        FETCH NEXT FROM c1 INTO @table_name 
108END 
109CLOSE c1 
110DEALLOCATE c1 
111
112SELECT  t1.* 
113       ,t2.schemaname  
114FROM #temp_Table t1  
115INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename ) 
116ORDER BY schemaname,t1.tablename; 
117
118DROP TABLE #temp_Table
119END
120	
121USE MyDatabase; GO
122
123EXEC sp_spaceused N'User.ContactInfo'; GO
124	
125USE MyDatabase; GO
126
127sp_msforeachtable 'EXEC sp_spaceused [?]' GO