常用sql备份

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

set nocount on 
exec sp_MSForEachTable 
@precommand=N'
create table ##(
id int identity,
表名 sysname,
字段数 int,
记录数 int,
保留空间 Nvarchar(20),
使用空间 varchar(20),
索引使用空间 varchar(20),
未用空间 varchar(20))',
@command1=N'insert ##(表名,记录数,保留空间,使用空间,索引使用空间,未用空间) exec sp_spaceused ''?''
        update ## set 字段数=(select count(*) from syscolumns where id=object_id(''?'')) where id=scope_identity()',
@postcommand=N'select 
    id,
    表名,
    字段数 列数,
    (记录数/10000.0) 记录数万,
    (Convert(bigint,rtrim(Replace(保留空间,''KB'','''')))/1024.0) 保留空间M,
    (Convert(bigint,rtrim(Replace(使用空间,''KB'','''')))/1024.0) 使用空间M,
    (Convert(bigint,rtrim(Replace(索引使用空间,''KB'','''')))/1024.0) 索引使用空间M,
    (Convert(bigint,rtrim(Replace(未用空间,''KB'','''')))/1024.0) 未用空间M
 from ## a order by 使用空间M desc;
 drop table ##'
set nocount off

统计分区信息

SELECT *,$partition.PartFunCreateTime(InsertTime) [Partition Number]
--, min(o.InsertTime) AS [Min StudentID]
--, max(o.InsertTime) AS [Max StudentID]
--, count(*) as [Rows In Partition]
FROM Person AS o
--GROUP BY $partition.PartFunCreateTime(InsertTime)
ORDER BY [Partition Number]

SELECT $partition.PartFunCreateTime(InsertTime) [Partition Number]
, min(o.InsertTime) AS [Min StudentID]
, max(o.InsertTime) AS [Max StudentID]
, count(*) as [Rows In Partition]
FROM Person AS o
GROUP BY $partition.PartFunCreateTime(InsertTime)
ORDER BY [Partition Number]

分区测试

BEGIN TRANSACTION
USE [FenQuTest]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[staging_Person_0](
    [ID] [int] NOT NULL,
    [InsertTime] [smalldatetime] NULL
) ON [fg1]

USE [FenQuTest]
ALTER TABLE [FenQuTest].[dbo].[Person] SWITCH PARTITION 1 TO [FenQuTest].[dbo].[staging_Person_0]PARTITION 1
USE [FenQuTest]
ALTER PARTITION FUNCTION [PartFunCreateTime]() MERGE RANGE(N'2013-01-01T00:00:00.000')
COMMIT TRANSACTION

表到分区

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