统计数据库中表格数据行数所占空间和索引情况
1 set nocount on 2 exec sp_MSForEachTable 3 @precommand=N' 4 create table ##( 5 id int identity, 6 表名 sysname, 7 字段数 int, 8 记录数 int, 9 保留空间 Nvarchar(20),10 使用空间 varchar(20),11 索引使用空间 varchar(20),12 未用空间 varchar(20))',13 @command1=N'insert ##(表名,记录数,保留空间,使用空间,索引使用空间,未用空间) exec sp_spaceused ''?''14 update ## set 字段数=(select count(*) from syscolumns where id=object_id(''?'')) where id=scope_identity()',15 @postcommand=N'select 16 id,17 表名,18 字段数 列数,19 (记录数/10000.0) 记录数万,20 (Convert(bigint,rtrim(Replace(保留空间,''KB'','''')))/1024.0) 保留空间M,21 (Convert(bigint,rtrim(Replace(使用空间,''KB'','''')))/1024.0) 使用空间M,22 (Convert(bigint,rtrim(Replace(索引使用空间,''KB'','''')))/1024.0) 索引使用空间M,23 (Convert(bigint,rtrim(Replace(未用空间,''KB'','''')))/1024.0) 未用空间M24 from ## a order by 使用空间M desc;25 drop table ##'26 set nocount off 统计分区信息
1 SELECT *,$partition.PartFunCreateTime(InsertTime) [Partition Number] 2 --, min(o.InsertTime) AS [Min StudentID] 3 --, max(o.InsertTime) AS [Max StudentID] 4 --, count(*) as [Rows In Partition] 5 FROM Person AS o 6 --GROUP BY $partition.PartFunCreateTime(InsertTime) 7 ORDER BY [Partition Number] 8 9 SELECT $partition.PartFunCreateTime(InsertTime) [Partition Number]10 , min(o.InsertTime) AS [Min StudentID]11 , max(o.InsertTime) AS [Max StudentID]12 , count(*) as [Rows In Partition]13 FROM Person AS o14 GROUP BY $partition.PartFunCreateTime(InsertTime)15 ORDER BY [Partition Number]
分区测试
1 BEGIN TRANSACTION 2 USE [FenQuTest] 3 SET ANSI_NULLS ON 4 SET QUOTED_IDENTIFIER ON 5 CREATE TABLE [dbo].[staging_Person_0]( 6 [ID] [int] NOT NULL, 7 [InsertTime] [smalldatetime] NULL 8 ) ON [fg1] 9 10 USE [FenQuTest]11 ALTER TABLE [FenQuTest].[dbo].[Person] SWITCH PARTITION 1 TO [FenQuTest].[dbo].[staging_Person_0]PARTITION 112 USE [FenQuTest]13 ALTER PARTITION FUNCTION [PartFunCreateTime]() MERGE RANGE(N'2013-01-01T00:00:00.000')14 COMMIT TRANSACTION
表到分区
1 ALTER TABLE [FenQuTest].[dbo].[staging_Person_0] SWITCH TO [FenQuTest].[dbo].[Person]PARTITION 1