獲取資料庫表的資訊(大小,索引大小,建立時間,行數)
-- =============================================
-- Author:
-- Create date:
-- Description:
--Thanks to ,, RBarry Young
-- =============================================
CREATE PROCEDURE [dbo].[spTableInformation]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
SCHEMA_NAME ( SYSTBL.SCHEMA_ID ) AS [SCHEMA] ,
SYSTBL.NAME ,
coalesce
(
(
SELECT NAME FROM sys.database_principals AS SDBP
WHERE ( PRINCIPAL_ID = SYSTBL.PRINCIPAL_ID )
) , SCHEMA_NAME ( SYSTBL.SCHEMA_ID )
)
AS OWNER ,
SYSTBL.MAX_COLUMN_ID_USED AS COLUMNS ,
cast
(
CASE SINDX_1.INDEX_ID
WHEN 1 THEN 1
ELSE 0
END
AS bit
)
AS HASCLUSIDX ,
coalesce
(
(
SELECT sum ( rows ) FROM sys.partitions AS SPART
WHERE ( object_id = SYSTBL.OBJECT_ID )
AND
( INDEX_ID < 2 )
) , 0
)
AS [ROWCOUNT] ,
coalesce
(
(
SELECT cast ( SPTV.low / 1024.0 AS float ) * sum
(
SAU_1.USED_PAGES -
CASE
WHEN SAU_1.TYPE <> 1 THEN SAU_1.USED_PAGES
WHEN SYSP.INDEX_ID < 2 THEN SAU_1.DATA_PAGES
ELSE 0
END
)
FROM sys.indexes AS SINDX_2
INNER JOIN sys.partitions AS SYSP ON SYSP.OBJECT_ID = SINDX_2.OBJECT_ID AND SYSP.INDEX_ID = SINDX_2.INDEX_ID
INNER JOIN sys.allocation_units AS SAU_1 ON SAU_1.CONTAINER_ID = SYSP.PARTITION_ID
WHERE ( SINDX_2.OBJECT_ID = SYSTBL.OBJECT_ID )
) , 0.0
)
AS INDEXKB ,
coalesce
(
(
SELECT cast ( SPTV.low / 1024.0 AS float ) * sum
(
CASE
WHEN SAU_2.TYPE <> 1 THEN SAU_2.USED_PAGES
WHEN SYSP.INDEX_ID < 2 THEN SAU_2.DATA_PAGES
ELSE 0
END
)
AS Expr1 FROM sys.indexes AS SINDX_2
INNER JOIN sys.partitions AS SYSP ON SYSP.OBJECT_ID = SINDX_2.OBJECT_ID AND SYSP.INDEX_ID = SINDX_2.INDEX_ID
INNER JOIN sys.allocation_units AS SAU_2 ON SAU_2.CONTAINER_ID = SYSP.PARTITION_ID
WHERE ( SINDX_2.OBJECT_ID = SYSTBL.OBJECT_ID )
) , 0.0
)
AS DATAKB ,
SYSTBL.CREATE_DATE ,
SYSTBL.MODIFY_DATE
FROM sys.tables AS SYSTBL
INNER JOIN sys.indexes AS SINDX_1 ON SINDX_1.OBJECT_ID = SYSTBL.OBJECT_ID AND SINDX_1.INDEX_ID < 2
INNER JOIN master.dbo.spt_values AS SPTV ON SPTV.NUMBER = 1 AND SPTV.type = 'E'
END
GO
-- Author:
-- Create date:
-- Description:
--Thanks to ,, RBarry Young
-- =============================================
CREATE PROCEDURE [dbo].[spTableInformation]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
SCHEMA_NAME ( SYSTBL.SCHEMA_ID ) AS [SCHEMA] ,
SYSTBL.NAME ,
coalesce
(
(
SELECT NAME FROM sys.database_principals AS SDBP
WHERE ( PRINCIPAL_ID = SYSTBL.PRINCIPAL_ID )
) , SCHEMA_NAME ( SYSTBL.SCHEMA_ID )
)
AS OWNER ,
SYSTBL.MAX_COLUMN_ID_USED AS COLUMNS ,
cast
(
CASE SINDX_1.INDEX_ID
WHEN 1 THEN 1
ELSE 0
END
AS bit
)
AS HASCLUSIDX ,
coalesce
(
(
SELECT sum ( rows ) FROM sys.partitions AS SPART
WHERE ( object_id = SYSTBL.OBJECT_ID )
AND
( INDEX_ID < 2 )
) , 0
)
AS [ROWCOUNT] ,
coalesce
(
(
SELECT cast ( SPTV.low / 1024.0 AS float ) * sum
(
SAU_1.USED_PAGES -
CASE
WHEN SAU_1.TYPE <> 1 THEN SAU_1.USED_PAGES
WHEN SYSP.INDEX_ID < 2 THEN SAU_1.DATA_PAGES
ELSE 0
END
)
FROM sys.indexes AS SINDX_2
INNER JOIN sys.partitions AS SYSP ON SYSP.OBJECT_ID = SINDX_2.OBJECT_ID AND SYSP.INDEX_ID = SINDX_2.INDEX_ID
INNER JOIN sys.allocation_units AS SAU_1 ON SAU_1.CONTAINER_ID = SYSP.PARTITION_ID
WHERE ( SINDX_2.OBJECT_ID = SYSTBL.OBJECT_ID )
) , 0.0
)
AS INDEXKB ,
coalesce
(
(
SELECT cast ( SPTV.low / 1024.0 AS float ) * sum
(
CASE
WHEN SAU_2.TYPE <> 1 THEN SAU_2.USED_PAGES
WHEN SYSP.INDEX_ID < 2 THEN SAU_2.DATA_PAGES
ELSE 0
END
)
AS Expr1 FROM sys.indexes AS SINDX_2
INNER JOIN sys.partitions AS SYSP ON SYSP.OBJECT_ID = SINDX_2.OBJECT_ID AND SYSP.INDEX_ID = SINDX_2.INDEX_ID
INNER JOIN sys.allocation_units AS SAU_2 ON SAU_2.CONTAINER_ID = SYSP.PARTITION_ID
WHERE ( SINDX_2.OBJECT_ID = SYSTBL.OBJECT_ID )
) , 0.0
)
AS DATAKB ,
SYSTBL.CREATE_DATE ,
SYSTBL.MODIFY_DATE
FROM sys.tables AS SYSTBL
INNER JOIN sys.indexes AS SINDX_1 ON SINDX_1.OBJECT_ID = SYSTBL.OBJECT_ID AND SINDX_1.INDEX_ID < 2
INNER JOIN master.dbo.spt_values AS SPTV ON SPTV.NUMBER = 1 AND SPTV.type = 'E'
END
GO
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16436858/viewspace-609719/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mssql 獲取表空間大小SQL
- MySQL 庫大小、表大小、索引大小查詢命令MySql索引
- java 獲取資料夾大小、檔案大小、檔案個數Java
- 查詢oracle 表的大小和表的建立時間Oracle
- Oracle資料庫表空間的資料檔案大小上限。Oracle資料庫
- 修改Oracle資料庫表的大小Oracle資料庫
- linux 獲取磁碟空間大小Linux
- 獲取bitmap大小
- 檢視Oracle資料庫表空間大小,是否需要增加表空間的資料檔案Oracle資料庫
- DB2頁大小、表大小和表空間大小限制DB2
- 查詢MySQL資料庫,MySQL表的大小MySql資料庫
- 【VIEW】建立檢視快速獲取Library Cache中超過50KB大小的資料庫物件View資料庫物件
- [Mysql]檢視每個資料庫大小以及每個表最後的修改時間MySql資料庫
- oracle 回收表空間的資料檔案大小Oracle
- [轉帖]達夢資料庫-統計資料表資料量及空間表大小資料庫
- java 獲取物件大小Java物件
- Oracle如何預估將要建立的索引和表的大小Oracle索引
- GreenPlum 獲取表儲存大小的方法推薦
- 計算表資料真實行長度獲得表真實大小
- 表空間資料檔案建立大小與實際使用大小以及rman備份集的關係系列一
- 在MySQL中,如何獲取資料庫下所有表的資料行數?MySql資料庫
- MYSQL-檢視資料庫或表的大小MySql資料庫
- 達夢資料庫表空間等空間大小查詢方法總結資料庫
- oracle如何估算即將建立的索引大小Oracle索引
- 【oracle 】如何估算即將建立的索引大小Oracle索引
- JS獲取螢幕大小JS
- SQL Server 2005 檢視資料庫表的大小 按照表大小排列SQLServer資料庫
- 表空間中有資料也可以壓縮表空間(資料檔案)大小
- 查詢資料庫每個表佔用的大小資料庫
- sqlServer的資料庫回縮與表大小檢查。SQLServer資料庫
- oracle資料庫獲取指定表的列的相關資訊Oracle資料庫
- POST表單資料大小的限制
- 查詢資料庫大小資料庫
- 獲取網路圖片的大小
- 獲取上傳檔案的大小
- 如何檢視Oracle資料庫表空間大小(空閒、已使用),是否要增加表空間的資料檔案...Oracle資料庫
- 表空間大小查詢
- 【sga】資料庫啟動時的的SGA大小顯示資料庫