연구개발/DBA
각 데이터베이스의 사용량 확인
HEAD1TON
2010. 6. 18. 11:04
dba_spaceused.ziphttp://www.novicksoftware.com/Articles/sql-server-table-space-reporting-sp_spaceused.htm
1 CREATE PROC [dbo].[dba_SpaceUsed]
2
3 @SourceDB varchar ( 128 ) = null -- Optional database name
4 -- If omitted, the current database is reported.
5 , @SortBy char(1) = 'S' -- N for name, S for Size
6 -- T for table name
7
8 /* Returns a table with the space used in all tables of the
9 * database. It's reported with the schema information unlike
10 * the system procedure sp_spaceuse.
11 *
12 * sp_spaceused is used to perform the calculations to ensure
13 * that the numbers match what SQL Server would report.
14 *
15 * Compatible with sQL Server 2000 and 2005
16 *
17 * Example:
18 exec dbo.dba_SpaceUsed null, 'T'
19 *
20 * © Copyright 2007 Andrew Novick http://www.NovickSoftware.com
21 * This software is provided as is without warrentee of any kind.
22 * You may use this procedure in any of your SQL Server databases
23 * including databases that you sell, so long as they contain
24 * other unrelated database objects. You may not publish this
25 * procedure either in print or electronically.
26 ******************************************************************/
27 AS
28
29 SET NOCOUNT ON
30
31 DECLARE @sql nvarchar (4000)
32
33 IF @SourceDB IS NULL BEGIN
34 SET @SourceDB = DB_NAME () -- The current DB
35 END
36
37 --------------------------------------------------------
38 -- Create and fill a list of the tables in the database.
39
40 CREATE TABLE #Tables ( [schema] sysname
41 , TabName sysname )
42
43 SELECT @sql = 'insert #tables ([schema], [TabName])
44 select TABLE_SCHEMA, TABLE_NAME
45 from ['+ @SourceDB +'].INFORMATION_SCHEMA.TABLES
46 where TABLE_TYPE = ''BASE TABLE'''
47 EXEC (@sql)
48
49
50 ---------------------------------------------------------------
51 -- #TabSpaceTxt Holds the results of sp_spaceused.
52 -- It Doesn't have Schema Info!
53 CREATE TABLE #TabSpaceTxt (
54 TabName sysname
55 , [Rows] varchar (11)
56 , Reserved varchar (18)
57 , Data varchar (18)
58 , Index_Size varchar ( 18 )
59 , Unused varchar ( 18 )
60 )
61
62 ---------------------------------------------------------------
63 -- The result table, with numeric results and Schema name.
64 CREATE TABLE #TabSpace ( [Schema] sysname
65 , TabName sysname
66 , [Rows] bigint
67 , ReservedMB numeric(18,3)
68 , DataMB numeric(18,3)
69 , Index_SizeMB numeric(18,3)
70 , UnusedMB numeric(18,3)
71 )
72
73 DECLARE @Tab sysname -- table name
74 , @Sch sysname -- owner,schema
75
76 DECLARE TableCursor CURSOR FOR
77 SELECT [SCHEMA], TabNAME
78 FROM #tables
79
80 OPEN TableCursor;
81 FETCH TableCursor into @Sch, @Tab;
82
83 WHILE @@FETCH_STATUS = 0 BEGIN
84
85 SELECT @sql = 'exec [' + @SourceDB
86 + ']..sp_executesql N''insert #TabSpaceTxt exec sp_spaceused '
87 + '''''[' + @Sch + '].[' + @Tab + ']' + '''''''';
88
89 Delete from #TabSpaceTxt; -- Stores 1 result at a time
90 EXEC (@sql);
91
92 INSERT INTO #TabSpace
93 SELECT @Sch
94 , [TabName]
95 , convert(bigint, rows)
96 , convert(numeric(18,3), convert(numeric(18,3),
97 left(reserved, len(reserved)-3)) / 1024.0)
98 ReservedMB
99 , convert(numeric(18,3), convert(numeric(18,3),
100 left(data, len(data)-3)) / 1024.0) DataMB
101 , convert(numeric(18,3), convert(numeric(18,3),
102 left(index_size, len(index_size)-3)) / 1024.0)
103 Index_SizeMB
104 , convert(numeric(18,3), convert(numeric(18,3),
105 left(unused, len([Unused])-3)) / 1024.0)
106 [UnusedMB]
107 FROM #TabSpaceTxt;
108
109 FETCH TableCursor into @Sch, @Tab;
110 END;
111
112 CLOSE TableCursor;
113 DEALLOCATE TableCursor;
114
115 -----------------------------------------------------
116 -- Caller specifies sort, Default is size
117 IF @SortBy = 'N' -- Use Schema then Table Name
118 SELECT * FROM #TabSpace
119 ORDER BY [Schema] asc, [TabName] asc
120 ELSE IF @SortBy = 'T' -- Table name, then schema
121 SELECT * FROM #TabSpace
122 ORDER BY [TabName] asc, [Schema] asc
123 ELSE -- S, NULL, or whatever get's the default
124 SELECT * FROM #TabSpace
125 ORDER BY ReservedMB desc
126 ;
127
128 DROP TABLE #Tables
129 DROP TABLE #TabSpaceTxt
130 DROP TABLE #TabSpace
131