常用sql备份

统计数据库中表格数据行数所占空间和索引情况

 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) 未用空间M
24  from ## a order by 使用空间M desc;
25  drop table ##'
26 set nocount off

View Code

统计分区信息

 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 o
14 GROUP BY $partition.PartFunCreateTime(InsertTime)
15 ORDER BY [Partition Number]

View Code

分区测试

 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 1
12 USE [FenQuTest]
13 ALTER PARTITION FUNCTION [PartFunCreateTime]() MERGE RANGE(N'2013-01-01T00:00:00.000')
14 COMMIT TRANSACTION

View Code

表到分区

1 ALTER TABLE [FenQuTest].[dbo].[staging_Person_0] SWITCH TO [FenQuTest].[dbo].[Person]PARTITION 1

View Code