SQL SERVERで指定されたデータベースの各テーブルのデータ量と行ごとのレコードに使用される領域の表示
2026 ワード
SQL文
参考資料:SQL SERVERで指定されたデータベースの各テーブルのデータ量と行ごとのレコードに使用されるスペースを表示するhttp://www.studyofnet.com/news/1250.html
CREATE TABLE #tablespaceinfo
(
nameinfo VARCHAR(500) ,
rowsinfo BIGINT ,
reserved VARCHAR(20) ,
datainfo VARCHAR(20) ,
index_size VARCHAR(20) ,
unused VARCHAR(20)
)
DECLARE @tablename VARCHAR(255);
DECLARE Info_cursor CURSOR
FOR
SELECT '[' + s.[name] +']' +'.' + '[' + t.[name] + ']'
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE type = 'U';
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tablespaceinfo
EXEC sp_spaceused @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
--
CREATE TABLE [#tmptb]
(
TableName VARCHAR(50) ,
DataInfo BIGINT ,
RowsInfo BIGINT ,
Spaceperrow AS ( CASE RowsInfo
WHEN 0 THEN 0
ELSE CAST(DataInfo AS decimal(18,2))/CAST(RowsInfo AS decimal(18,2))
END ) PERSISTED
)
--
INSERT INTO [#tmptb]
( [TableName] ,
[DataInfo] ,
[RowsInfo]
)
SELECT [nameinfo] ,
CAST(REPLACE([datainfo], 'KB', '') AS BIGINT) AS 'datainfo' ,
[rowsinfo]
FROM #tablespaceinfo
ORDER BY CAST(REPLACE(reserved, 'KB', '') AS INT) DESC
--
SELECT [tbspinfo].* ,
[tmptb].[Spaceperrow] AS ' (KB)'
FROM [#tablespaceinfo] AS tbspinfo ,
[#tmptb] AS tmptb
WHERE [tbspinfo].[nameinfo] = [tmptb].[TableName]
ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS INT) DESC
DROP TABLE [#tablespaceinfo]
DROP TABLE [#tmptb]
参考資料:SQL SERVERで指定されたデータベースの各テーブルのデータ量と行ごとのレコードに使用されるスペースを表示するhttp://www.studyofnet.com/news/1250.html