SQL-Server 2005에서 특정 DB의 각 테이블 용량을 보고 싶습니다
저장함수(Stored Procedure)를 이용해서 될꺼 같은데..
sp_helpdb를 이용하면 연결된 DB 각각의 용량이 db_size 컬럼에 나오지만,
각 테이블의 file size는 알수가 없네요.
현재 SQL Server 2005 Enterprise 버전 사용중입니다.
SP2까지 설치했구요..
잘 아시는분 조언 부탁드립니다.
아래글로 DB 이름 주고 스토어 프로시져 생성후 실행 하세요. DBNAME -> 데이터베이스 이름 dba_SpaceUsed < 프로시져 이름..
USE [DBNAME] GO /****** Object: StoredProcedure [dbo].[dba_SpaceUsed] Script Date: 08/29/2008 17:31:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[dba_SpaceUsed]
@SourceDB varchar ( 128 ) = null -- Optional database name -- If omitted, the current database is reported. , @SortBy char(1) = 'S' -- N for name, S for Size -- T for table name
/* Returns a table with the space used in all tables of the * database. It's reported with the schema information unlike * the system procedure sp_spaceuse. * * sp_spaceused is used to perform the calculations to ensure * that the numbers match what SQL Server would report. * * Compatible with sQL Server 2000 and 2005 * * Example: exec dbo.dba_SpaceUsed null, 'N' * * © Copyright 2007 Andrew Novick http://www.NovickSoftware.com * This software is provided as is without warrentee of any kind. * You may use this procedure in any of your SQL Server databases * including databases that you sell, so long as they contain * other unrelated database objects. You may not publish this * procedure either in print or electronically. ******************************************************************/ AS
SET NOCOUNT ON
DECLARE @sql nvarchar (4000)
IF @SourceDB IS NULL BEGIN SET @SourceDB = DB_NAME() -- The current DB END
-------------------------------------------------------- -- Create and fill a list of the tables in the database.
CREATE TABLE #Tables ( [schema] sysname , TabName sysname ) SELECT @sql = 'insert #tables ([schema], [TabName]) select TABLE_SCHEMA, TABLE_NAME from ['+ @SourceDB +'].INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE''' exec (@sql)
--------------------------------------------------------------- -- #TabSpaceTxt Holds the results of sp_spaceused. -- It Doesn't have Schema Info! CREATE TABLE #TabSpaceTxt ( TabName sysname , [Rows] varchar (11) , Reserved varchar (18) , Data varchar (18) , Index_Size varchar ( 18 ) , Unused varchar ( 18 ) ) --------------------------------------------------------------- -- The result table, with numeric results and Schema name. CREATE TABLE #TabSpace ( [Schema] sysname , TabName sysname , [Rows] bigint , ReservedMB numeric(18,3) , DataMB numeric(18,3) , Index_SizeMB numeric(18,3) , UnusedMB numeric(18,3) )
DECLARE @Tab sysname -- table name , @Sch sysname -- owner,schema
DECLARE TableCursor CURSOR FOR SELECT [SCHEMA], TabNAME FROM #tables
OPEN TableCursor; FETCH TableCursor into @Sch, @Tab;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @sql = 'exec [' + @SourceDB + ']..sp_executesql N''insert #TabSpaceTxt exec sp_spaceused ' + '''''[' + @Sch + '].[' + @Tab + ']' + '''''''';
Delete from #TabSpaceTxt; -- Stores 1 result at a time EXEC (@sql);
INSERT INTO #TabSpace SELECT @Sch , [TabName] , convert(bigint, rows) , convert(numeric(18,3), convert(numeric(18,3), left(reserved, len(reserved)-3)) / 1024.0) ReservedMB , convert(numeric(18,3), convert(numeric(18,3), left(data, len(data)-3)) / 1024.0) DataMB , convert(numeric(18,3), convert(numeric(18,3), left(index_size, len(index_size)-3)) / 1024.0) Index_SizeMB , convert(numeric(18,3), convert(numeric(18,3), left(unused, len([Unused])-3)) / 1024.0) [UnusedMB] FROM #TabSpaceTxt;
FETCH TableCursor into @Sch, @Tab; END;
CLOSE TableCursor; DEALLOCATE TableCursor;
----------------------------------------------------- -- Caller specifies sort, Default is size IF @SortBy = 'N' -- Use Schema then Table Name SELECT * FROM #TabSpace ORDER BY [Schema] asc, [TabName] asc ELSE IF @SortBy = 'T' -- Table name, then schema SELECT * FROM #TabSpace ORDER BY [TabName] asc, [Schema] asc ELSE -- S, NULL, or whatever get's the default SELECT * FROM #TabSpace ORDER BY ReservedMB desc ;
DROP TABLE #Tables DROP TABLE #TabSpaceTxt DROP TABLE #TabSpace
