use masterdeclare @DbName varchar(255),@dir varchar(256),@dir_db varchar(256), @verb varchar(256),@cmd varchar(256), @backup_name varchar(256),@dynamic_name varchar(10), @disk_name varchar(256),@copy nvarchar(100), @today datetime,@weekday int--建立网络连接 exec xp_cmdshell 'net use K: /delete' exec xp_cmdshell 'net use I: /delete'exec xp_cmdshell 'net use K: \\193.254.40.118\backup backup /user:Web\backup ' exec xp_cmdshell 'net use I: \\172.16.8.48\databackup backup /user:QA-SERVER-TEST\backup'--设定名字 set @today=getdate() set @dynamic_name=convert(varchar(10),@today,120)set @dir='K:\'+@dynamic_name set @dir_db=@dir+'Database' set @verb='mkdir '--建立目录 set @cmd=@verb+@dir_db exec xp_cmdshell @cmd declare cur_database cursor forward_only read_only for select name from sysdatabases where dbid>4 --系统数据库的dbid<=4open cur_database fetch next from cur_database into @DbNamewhile @@fetch_status=0 begin set @backup_name= @DbName+'_'+@dynamic_name set @disk_name=@dir_db+'\'+@backup_name+'.bak'
set @weekday= datepart(dw,@today) if (@weekday=6) --如果是周五,则进行完全备份 BACKUP DATABASE @DbName TO @backup_name else --其他时候进行差异备份 BACKUP DATABASE @DbName TO @backup_name with differential --清理日志 backup log @DbName with no_log --释放设备 exec sp_dropdevice @backup_name
--复制备份副本到其他地方 set @copy='copy '+@disk_name+' I:' exec xp_cmdshell @copy
--备份下一个数据库 fetch next from cur_database into @DbName end close cur_database deallocate cur_database--删除网络连接 exec xp_cmdshell 'net use K: /delete' exec xp_cmdshell 'net use I: /delete' /**//****************************************************************************** * * File Name : Restore.sql * Function : 数据库还原 * Author : Yahong<[email protected]> * Version : 00 * Date : 2007-09-18 * Re : * *******************************************************************************/ use masterdeclare @DbName varchar(255) --数据库的名字 ,@WholeFileName varchar(255) --完全备份的文件名 ,@DifferentFileName varchar(255) --差异备份的文件名 ,@MasterFileName varchar(255) --数据文件名,注意他们都是逻辑名称 ,@LogFileName varchar(255) --日志文件名 ,@TargetDir varchar(255) --还原后数据库文件所在的路径,如果没有指定该参数, --则必须存在与原数据库相同的路径declare @WholeDeviceName varchar(255) ,@DifferenctDeviceName varchar(255) ,@TargetMasterFileName varchar(255) ,@TargetLogFileName varchar(255)--建立网络链接 exec xp_cmdshell 'net use K: \\172.16.8.48\200709 backup /User:qa-server-test\backup'--在这里设置需要备份的文件等信息 set @DbName='CCTQA' --需要还原的数据库的名字,注意不要搞错了,否则 --覆盖了其他的数据库,可别说我没有提醒你 set @WholeFileName='CCTQA_2007-09-14.bak' --完全备份文件--以下4行如果没有,不要指定,把他们注释掉就行了 set @DifferentFileName='CCTQA_2007-09-17.bak' --最后一次差异备份文件 set @MasterFileName='CCTQA_Data' --数据文件 set @LogFileName='CCTQA_Log' --日志文件 set @TargetDir='D:\CCTQA\Databae' --目标路径--设置目标路径 set @TargetMasterFileName=@TargetDir+'\'+@MasterFileName set @TargetLogFileName=@TargetDir+'\'+@LogFileName--添加还原设备 set @WholeDeviceName=@DbName+'WholeDevice' set @WholeFileName='K:\'+@WholeFileName exec sp_addumpdevice 'disk',@WholeDeviceName,@WholeFileName--开始备份 if(isnull(@DifferentFileName,'')<>'') --如果具有差异备份的还原 begin --添加差异备份还原的设备 set @DifferenctDeviceName=@DbName+'DifferenctDevice' set @DifferentFileName='K:\'+@DifferentFileName exec sp_addumpdevice 'disk',@DifferenctDeviceName,@DifferentFileName --备份 if(isnull(@TargetDir,'')='') restore database @DbName from @WholeDeviceName with NORECOVERY else --如果还原后的数据库文件的路径与备份前的路径不一致 restore database @DbName from @WholeDeviceName with NORECOVERY, move @MasterFileName to @TargetMasterFileName, move @LogFileName to @TargetLogFileName restore database @DbName from @DifferenctDeviceName end else begin --只有完全备份的还原 if(isnull(@TargetDir,'')='') restore database @DbName from @WholeFileName else restore database @DbName from @WholeFileName with move @MasterFileName to @TargetMasterFileName, move @LogFileName to @TargetLogFileName end--释放备份设备 exec sp_dropdevice @WholeDeviceName if(isnull(@DifferentFileName,'')<>'') exec sp_dropdevice @DifferenctDeviceName--删除网络链接 exec xp_cmdshell 'net use K: /delete'/**//************************************************************************ * * File Name : ShrinkLog.sql * Function : 收缩数据库的日志文件 * Author :Yahong<[email protected]> * Version : 00 * Date : 2007-09-16 * Re : * *************************************************************************/-- --第一步:设置需要收缩的数据库,找到需要收缩数据文件 --use cctqa select Size/128 Size,Name from sysfiles/**//*declare @LogName varchar(255),@TargetSize int-- -- --第二步:设置需要收缩的日志文件的逻辑名字和收缩后的大小 --千万不要搞错了,选错了文件,有可能会丢失数据,那时候 --哭都哭不回来了。 -- --set @LogName='CCTQA_Log' set @TargetSize=1declare @str varchar(300), @DatabaseName varchar(255) set @DatabaseName=db_name()if(not exists(select * from sysfiles where name=@LogName)) begin set @str='没有找到日志文件'+@LogName raiserror(@str,0,1) end else begin declare @curSize int,@maxTime int set @maxTime=10 set @curSize=(select size from sysfiles where name=@LogName)/128 print '收缩之前的日志文件的大小是:'+cast(@curSize as varchar(10))+'MB'
while (@curSize>@TargetSize) and (@maxTime>0) begin backup log @DatabaseName with no_log DBCC SHRINKFILE(@LogName,@TargetSize)
set @curSize=(select size from sysfiles where name=@LogName)/128 set @maxTime=@maxTime-1 end
set @curSize=(select size from sysfiles where name=@LogName)/128 print '收缩之后的日志文件的大小是:'+cast(@curSize as varchar(10))+'MB' end */
--sql server 2005 -- 1. 表结构信息查询 -- ======================================================================== -- 表结构信息查询 -- 邹建 2005.08(引用请保留此信息) -- ======================================================================== SELECT TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END, TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''), Column_id=C.column_id, ColumnName=C.name, PrimaryKey=ISNULL(IDX.PrimaryKey,N''), [IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END, Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END, Type=T.name, Length=C.max_length, Precision=C.precision, Scale=C.scale, NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END, [Default]=ISNULL(D.definition,N''), ColumnDesc=ISNULL(PFD.[value],N''), IndexName=ISNULL(IDX.IndexName,N''), IndexSort=ISNULL(IDX.Sort,N''), Create_Date=O.Create_Date, Modify_Date=O.Modify_date FROM sys.columns C INNER JOIN sys.objects O ON C.[object_id]=O.[object_id] AND O.type='U' AND O.is_ms_shipped=0 INNER JOIN sys.types T ON C.user_type_id=T.user_type_id LEFT JOIN sys.default_constraints D ON C.[object_id]=D.parent_object_id AND C.column_id=D.parent_column_id AND C.default_object_id=D.[object_id] LEFT JOIN sys.extended_properties PFD ON PFD.class=1 AND C.[object_id]=PFD.major_id AND C.column_id=PFD.minor_id -- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) LEFT JOIN sys.extended_properties PTB ON PTB.class=1 AND PTB.minor_id=0 AND C.[object_id]=PTB.major_id -- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) LEFT JOIN -- 索引及主键信息 ( SELECT IDXC.[object_id], IDXC.column_id, Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END, IndexName=IDX.Name FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id LEFT JOIN sys.key_constraints KC ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息 ( SELECT [object_id], Column_id, index_id=MIN(index_id) FROM sys.index_columns GROUP BY [object_id], Column_id ) IDXCUQ ON IDXC.[object_id]=IDXCUQ.[object_id] AND IDXC.Column_id=IDXCUQ.Column_id AND IDXC.index_id=IDXCUQ.index_id ) IDX ON C.[object_id]=IDX.[object_id] AND C.column_id=IDX.column_id -- WHERE O.name=N'要查询的表' -- 如果只查询指定表,加上此条件 ORDER BY O.name,C.column_id -- 2. 索引及主键信息 -- ======================================================================== -- 索引及主键信息 -- 邹建 2005.08(引用请保留此信息) -- ======================================================================== SELECT TableId=O.[object_id], TableName=O.Name, IndexId=ISNULL(KC.[object_id],IDX.index_id), IndexName=IDX.Name, IndexType=ISNULL(KC.type_desc,'Index'), Index_Column_id=IDXC.index_column_id, ColumnID=C.Column_id, ColumnName=C.Name, Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END, [UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END, Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END, Disabled=CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END, Fill_factor=IDX.fill_factor, Padded=CASE WHEN IDX.is_padded=1 THEN N'√'ELSE N'' END FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id LEFT JOIN sys.key_constraints KC ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id INNER JOIN sys.objects O ON O.[object_id]=IDX.[object_id] INNER JOIN sys.columns C ON O.[object_id]=C.[object_id] AND O.type='U' AND O.is_ms_shipped=0 AND IDXC.Column_id=C.Column_id -- INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息 -- ( -- SELECT [object_id], Column_id, index_id=MIN(index_id) -- FROM sys.index_columns -- GROUP BY [object_id], Column_id -- ) IDXCUQ -- ON IDXC.[object_id]=IDXCUQ.[object_id] -- AND IDXC.Column_id=IDXCUQ.Column_id
分区的: -- 进行演示操作前, 先备份, 以便可以在演示完成后, 恢复到原始状态 USE master -- 备份 BACKUP DATABASE AdventureWorks TO DISK = 'AdventureWorks.bak' WITH FORMAT---- 恢复 --RESTORE DATABASE AdventureWorks -- FROM DISK = 'AdventureWorks.bak' -- WITH REPLACE GO--========================================= -- 转换为分区表 --========================================= USE AdventureWorks GO-- 1. 创建分区函数 -- a. 适用于存储历史存档记录的分区表的分区函数 DECLARE @dt datetime SET @dt = '20020101' CREATE PARTITION FUNCTION PF_HistoryArchive(datetime) AS RANGE RIGHT FOR VALUES( @dt, DATEADD(Year, 1, @dt))-- b. 适用于存储历史记录的分区表的分区函数 --DECLARE @dt datetime SET @dt = '20030901' CREATE PARTITION FUNCTION PF_History(datetime) AS RANGE RIGHT FOR VALUES( @dt, DATEADD(Month, 1, @dt), DATEADD(Month, 2, @dt), DATEADD(Month, 3, @dt), DATEADD(Month, 4, @dt), DATEADD(Month, 5, @dt), DATEADD(Month, 6, @dt), DATEADD(Month, 7, @dt), DATEADD(Month, 8, @dt), DATEADD(Month, 9, @dt), DATEADD(Month, 10, @dt), DATEADD(Month, 11, @dt), DATEADD(Month, 12, @dt)) GO-- 2. 创建分区架构 -- a. 适用于存储历史存档记录的分区表的分区架构 CREATE PARTITION SCHEME PS_HistoryArchive AS PARTITION PF_HistoryArchive TO([PRIMARY], [PRIMARY], [PRIMARY])-- b. 适用于存储历史记录的分区表的分区架构 CREATE PARTITION SCHEME PS_History AS PARTITION PF_History TO([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]) GO-- 3. 删除索引 -- a. 删除存储历史存档记录的表中的索引 DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ProductID DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID-- b. 删除存储历史记录的表中的索引 DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ProductID DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID GO-- 4. 转换为分区表 -- a. 将存储历史存档记录的表转换为分区表 ALTER TABLE Production.TransactionHistoryArchive DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID WITH( MOVE TO PS_HistoryArchive(TransactionDate))-- b.将存储历史记录的表转换为分区表 ALTER TABLE Production.TransactionHistory DROP CONSTRAINT PK_TransactionHistory_TransactionID WITH( MOVE TO PS_History(TransactionDate)) GO-- 5. 恢复主键 -- a. 恢复存储历史存档记录的分区表的主键 ALTER TABLE Production.TransactionHistoryArchive ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED( TransactionID, TransactionDate)-- b. 恢复存储历史记录的分区表的主键 ALTER TABLE Production.TransactionHistory ADD CONSTRAINT PK_TransactionHistory_TransactionID PRIMARY KEY CLUSTERED( TransactionID, TransactionDate) GO-- 6. 恢复索引 -- a. 恢复存储历史存档记录的分区表的索引 CREATE INDEX IX_TransactionHistoryArchive_ProductID ON Production.TransactionHistoryArchive( ProductID)CREATE INDEX IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID ON Production.TransactionHistoryArchive( ReferenceOrderID, ReferenceOrderLineID)-- b. 恢复存储历史记录的分区表的索引 CREATE INDEX IX_TransactionHistory_ProductID ON Production.TransactionHistory( ProductID)CREATE INDEX IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID ON Production.TransactionHistory( ReferenceOrderID, ReferenceOrderLineID) GO-- 7. 查看分区表的相关信息 SELECT SchemaName = S.name, TableName = TB.name, PartitionScheme = PS.name, PartitionFunction = PF.name, PartitionFunctionRangeType = CASE WHEN boundary_value_on_right = 0 THEN 'LEFT' ELSE 'RIGHT' END, PartitionFunctionFanout = PF.fanout, SchemaID = S.schema_id, ObjectID = TB.object_id, PartitionSchemeID = PS.data_space_id, PartitionFunctionID = PS.function_id FROM sys.schemas S INNER JOIN sys.tables TB ON S.schema_id = TB.schema_id INNER JOIN sys.indexes IDX on TB.object_id = IDX.object_id AND IDX.index_id < 2 INNER JOIN sys.partition_schemes PS ON PS.data_space_id = IDX.data_space_id INNER JOIN sys.partition_functions PF ON PS.function_id = PF.function_id GO--========================================= -- 移动分区表数据 --========================================= -- 1. 为存储历史存档记录的分区表增加分区, 并接受从历史记录分区表移动过来的数据 -- a. 修改分区架构, 增加用以接受新分区的文件组 ALTER PARTITION SCHEME PS_HistoryArchive NEXT USED [PRIMARY]-- b. 修改分区函数, 增加分区用以接受从历史记录分区表移动过来的数据 DECLARE @dt datetime SET @dt = '20030901' ALTER PARTITION FUNCTION PF_HistoryArchive() SPLIT RANGE(@dt)-- c. 将历史记录表中的过期数据移动到历史存档记录表中 ALTER TABLE Production.TransactionHistory SWITCH PARTITION 2 TO Production.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(@dt)-- d. 将接受到的数据与原来的分区合并 ALTER PARTITION FUNCTION PF_HistoryArchive() MERGE RANGE(@dt) GO-- 2. 将存储历史记录的分区表中不包含数据的分区删除, 并增加新的分区以接受新数据 -- a. 合并不包含数据的分区 DECLARE @dt datetime SET @dt = '20030901' ALTER PARTITION FUNCTION PF_History() MERGE RANGE(@dt)-- b. 修改分区架构, 增加用以接受新分区的文件组 ALTER PARTITION SCHEME PS_History NEXT USED [PRIMARY]-- c. 修改分区函数, 增加分区用以接受新数据 SET @dt = '20041001' ALTER PARTITION FUNCTION PF_History() SPLIT RANGE(@dt) GO --========================================= -- 清除历史存档记录中的过期数据 --========================================= -- 1. 创建用于保存过期的历史存档数据的表 CREATE TABLE Production.TransactionHistoryArchive_2001_temp( TransactionID int NOT NULL, ProductID int NOT NULL, ReferenceOrderID int NOT NULL, ReferenceOrderLineID int NOT NULL DEFAULT ((0)), TransactionDate datetime NOT NULL DEFAULT (GETDATE()), TransactionType nchar(1) NOT NULL, Quantity int NOT NULL, ActualCost money NOT NULL, ModifiedDate datetime NOT NULL DEFAULT (GETDATE()), CONSTRAINT PK_TransactionHistoryArchive_2001_temp_TransactionID PRIMARY KEY CLUSTERED( TransactionID, TransactionDate) )-- 2. 将数据从历史存档记录分区表移动到第1步创建的表中 ALTER TABLE Production.TransactionHistoryArchive SWITCH PARTITION 1 TO Production.TransactionHistoryArchive_2001_temp-- 3. 删除不再包含数据的分区 DECLARE @dt datetime SET @dt = '20020101' ALTER PARTITION FUNCTION PF_HistoryArchive() MERGE RANGE(@dt)-- 4. 修改分区架构, 增加用以接受新分区的文件组 ALTER PARTITION SCHEME PS_HistoryArchive NEXT USED [PRIMARY]-- 5. 修改分区函数, 增加分区用以接受新数据 SET @dt = '20040101' ALTER PARTITION FUNCTION PF_HistoryArchive() SPLIT RANGE(@dt)
查询分区信息;WITH TBINFO AS( SELECT SchemaName = S.name, TableName = TB.name, PartitionScheme = PS.name, PartitionFunction = PF.name, PartitionFunctionRangeType = CASE WHEN boundary_value_on_right = 0 THEN 'LEFT' ELSE 'RIGHT' END, PartitionFunctionFanout = PF.fanout, SchemaID = S.schema_id, ObjectID = TB.object_id, PartitionSchemeID = PS.data_space_id, PartitionFunctionID = PS.function_id FROM sys.schemas S INNER JOIN sys.tables TB ON S.schema_id = TB.schema_id INNER JOIN sys.indexes IDX on TB.object_id = IDX.object_id AND IDX.index_id < 2 INNER JOIN sys.partition_schemes PS ON PS.data_space_id = IDX.data_space_id INNER JOIN sys.partition_functions PF ON PS.function_id = PF.function_id ), PF1 AS( SELECT PFP.function_id, PFR.boundary_id, PFR.value, Type = CONVERT(sysname, CASE T.name WHEN 'numeric' THEN 'decimal' WHEN 'real' THEN 'float' ELSE T.name END + CASE WHEN T.name IN('decimal', 'numeric') THEN QUOTENAME(RTRIM(PFP.precision) + CASE WHEN PFP.scale > 0 THEN ',' + RTRIM(PFP.scale) ELSE '' END, '()') WHEN T.name IN('float', 'real') THEN QUOTENAME(PFP.precision, '()') WHEN T.name LIKE 'n%char' THEN QUOTENAME(PFP.max_length / 2, '()') WHEN T.name LIKE '%char' OR T.name LIKE '%binary' THEN QUOTENAME(PFP.max_length, '()') ELSE '' END) FROM sys.partition_parameters PFP LEFT JOIN sys.partition_range_values PFR ON PFR.function_id = PFP.function_id AND PFR.parameter_id = PFP.parameter_id INNER JOIN sys.types T ON PFP.system_type_id = T.system_type_id ), PF2 AS( SELECT * FROM PF1 UNION ALL SELECT function_id, boundary_id = boundary_id - 1, value, type FROM PF1 WHERE boundary_id = 1 ), PF AS( SELECT B.function_id, boundary_id = ISNULL(B.boundary_id + 1, 1), value = STUFF( CASE WHEN A.boundary_id IS NULL THEN '' ELSE ' AND [partition_column_name] ' + PF.LessThan + ' ' + CONVERT(varchar(max), A.value) END + CASE WHEN A.boundary_id = 1 THEN '' ELSE ' AND [partition_column_name] ' + PF.MoreThan + ' ' + CONVERT(varchar(max), B.value) END, 1, 5, ''), B.Type FROM PF1 A RIGHT JOIN PF2 B ON A.function_id = B.function_id AND (A.boundary_id - 1 = B.boundary_id OR(A.boundary_id IS NULL AND B.boundary_id IS NULL)) INNER JOIN( SELECT function_id, LessThan = CASE WHEN boundary_value_on_right = 0 THEN '<=' ELSE '<' END, MoreThan = CASE WHEN boundary_value_on_right = 0 THEN '>' ELSE '>=' END FROM sys.partition_functions )PF ON B.function_id = PF.function_id ), PS AS( SELECT DDS.partition_scheme_id, DDS.destination_id, FileGroupName = FG.name, IsReadOnly = FG.is_read_only FROM sys.destination_data_spaces DDS INNER JOIN sys.filegroups FG ON DDS.data_space_id = FG.data_space_id ), PINFO AS( SELECT RowID = ROW_NUMBER() OVER(ORDER BY SchemaID, ObjectID, PS.destination_id), TB.SchemaName, TB.TableName, TB.PartitionScheme, PS.destination_id, PS.FileGroupName, PS.IsReadOnly, TB.PartitionFunction, TB.PartitionFunctionRangeType, TB.PartitionFunctionFanout, PF.boundary_id, PF.Type, PF.value FROM TBINFO TB INNER JOIN PS ON TB.PartitionSchemeID = PS.partition_scheme_id LEFT JOIN PF ON TB.PartitionFunctionID = PF.function_id AND PS.destination_id = PF.boundary_id ) SELECT RowID, SchemaName = CASE destination_id WHEN 1 THEN SchemaName ELSE N'' END, TableName = CASE destination_id WHEN 1 THEN TableName ELSE N'' END, PartitionScheme = CASE destination_id WHEN 1 THEN PartitionScheme ELSE N'' END, destination_id, FileGroupName, IsReadOnly, PartitionFunction = CASE destination_id WHEN 1 THEN PartitionFunction ELSE N'' END, PartitionFunctionRangeType = CASE destination_id WHEN 1 THEN PartitionFunctionRangeType ELSE N'' END, PartitionFunctionFanout = CASE destination_id WHEN 1 THEN CONVERT(varchar(20), PartitionFunctionFanout) ELSE N'' END, boundary_id = ISNULL(CONVERT(varchar(20), boundary_id), ''), Type = ISNULL(Type, N''), value = CASE PartitionFunctionFanout WHEN 1 THEN '<ALL Data>' ELSE ISNULL(value, N'<NEXT USED>') END FROM PINFO ORDER BY RowID
@verb varchar(256),@cmd varchar(256),
@backup_name varchar(256),@dynamic_name varchar(10),
@disk_name varchar(256),@copy nvarchar(100),
@today datetime,@weekday int--建立网络连接
exec xp_cmdshell 'net use K: /delete'
exec xp_cmdshell 'net use I: /delete'exec xp_cmdshell 'net use K: \\193.254.40.118\backup backup /user:Web\backup '
exec xp_cmdshell 'net use I: \\172.16.8.48\databackup backup /user:QA-SERVER-TEST\backup'--设定名字
set @today=getdate()
set @dynamic_name=convert(varchar(10),@today,120)set @dir='K:\'+@dynamic_name
set @dir_db=@dir+'Database'
set @verb='mkdir '--建立目录
set @cmd=@verb+@dir_db
exec xp_cmdshell @cmd
declare cur_database cursor forward_only read_only for
select name from sysdatabases
where dbid>4 --系统数据库的dbid<=4open cur_database fetch next from cur_database
into @DbNamewhile @@fetch_status=0
begin
set @backup_name= @DbName+'_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name+'.bak'
--添加备份设备
EXEC sp_addumpdevice 'disk',@backup_name, @disk_name
set @weekday= datepart(dw,@today)
if (@weekday=6) --如果是周五,则进行完全备份
BACKUP DATABASE @DbName TO @backup_name
else --其他时候进行差异备份
BACKUP DATABASE @DbName TO @backup_name with differential --清理日志
backup log @DbName with no_log --释放设备
exec sp_dropdevice @backup_name
--复制备份副本到其他地方
set @copy='copy '+@disk_name+' I:'
exec xp_cmdshell @copy
--备份下一个数据库
fetch next from cur_database
into @DbName
end
close cur_database
deallocate cur_database--删除网络连接
exec xp_cmdshell 'net use K: /delete'
exec xp_cmdshell 'net use I: /delete'
/**//******************************************************************************
*
* File Name : Restore.sql
* Function : 数据库还原
* Author : Yahong<[email protected]>
* Version : 00
* Date : 2007-09-18
* Re :
*
*******************************************************************************/
use masterdeclare
@DbName varchar(255) --数据库的名字
,@WholeFileName varchar(255) --完全备份的文件名
,@DifferentFileName varchar(255) --差异备份的文件名
,@MasterFileName varchar(255) --数据文件名,注意他们都是逻辑名称
,@LogFileName varchar(255) --日志文件名
,@TargetDir varchar(255) --还原后数据库文件所在的路径,如果没有指定该参数,
--则必须存在与原数据库相同的路径declare @WholeDeviceName varchar(255)
,@DifferenctDeviceName varchar(255)
,@TargetMasterFileName varchar(255)
,@TargetLogFileName varchar(255)--建立网络链接
exec xp_cmdshell 'net use K: \\172.16.8.48\200709 backup /User:qa-server-test\backup'--在这里设置需要备份的文件等信息
set @DbName='CCTQA' --需要还原的数据库的名字,注意不要搞错了,否则
--覆盖了其他的数据库,可别说我没有提醒你
set @WholeFileName='CCTQA_2007-09-14.bak' --完全备份文件--以下4行如果没有,不要指定,把他们注释掉就行了
set @DifferentFileName='CCTQA_2007-09-17.bak' --最后一次差异备份文件
set @MasterFileName='CCTQA_Data' --数据文件
set @LogFileName='CCTQA_Log' --日志文件
set @TargetDir='D:\CCTQA\Databae' --目标路径--设置目标路径
set @TargetMasterFileName=@TargetDir+'\'+@MasterFileName
set @TargetLogFileName=@TargetDir+'\'+@LogFileName--添加还原设备
set @WholeDeviceName=@DbName+'WholeDevice'
set @WholeFileName='K:\'+@WholeFileName
exec sp_addumpdevice 'disk',@WholeDeviceName,@WholeFileName--开始备份
if(isnull(@DifferentFileName,'')<>'') --如果具有差异备份的还原
begin
--添加差异备份还原的设备
set @DifferenctDeviceName=@DbName+'DifferenctDevice'
set @DifferentFileName='K:\'+@DifferentFileName
exec sp_addumpdevice 'disk',@DifferenctDeviceName,@DifferentFileName --备份
if(isnull(@TargetDir,'')='')
restore database @DbName from @WholeDeviceName
with NORECOVERY
else --如果还原后的数据库文件的路径与备份前的路径不一致
restore database @DbName from @WholeDeviceName
with NORECOVERY,
move @MasterFileName to @TargetMasterFileName,
move @LogFileName to @TargetLogFileName
restore database @DbName from @DifferenctDeviceName
end
else
begin --只有完全备份的还原
if(isnull(@TargetDir,'')='')
restore database @DbName from @WholeFileName
else
restore database @DbName from @WholeFileName
with move @MasterFileName to @TargetMasterFileName,
move @LogFileName to @TargetLogFileName
end--释放备份设备
exec sp_dropdevice @WholeDeviceName
if(isnull(@DifferentFileName,'')<>'')
exec sp_dropdevice @DifferenctDeviceName--删除网络链接
exec xp_cmdshell 'net use K: /delete'/**//************************************************************************
*
* File Name : ShrinkLog.sql
* Function : 收缩数据库的日志文件
* Author :Yahong<[email protected]>
* Version : 00
* Date : 2007-09-16
* Re :
*
*************************************************************************/--
--第一步:设置需要收缩的数据库,找到需要收缩数据文件
--use cctqa
select Size/128 Size,Name from sysfiles/**//*declare @LogName varchar(255),@TargetSize int--
--
--第二步:设置需要收缩的日志文件的逻辑名字和收缩后的大小
--千万不要搞错了,选错了文件,有可能会丢失数据,那时候
--哭都哭不回来了。
--
--set @LogName='CCTQA_Log'
set @TargetSize=1declare @str varchar(300), @DatabaseName varchar(255)
set @DatabaseName=db_name()if(not exists(select * from sysfiles where name=@LogName))
begin
set @str='没有找到日志文件'+@LogName
raiserror(@str,0,1)
end else
begin
declare @curSize int,@maxTime int
set @maxTime=10
set @curSize=(select size from sysfiles where name=@LogName)/128
print '收缩之前的日志文件的大小是:'+cast(@curSize as varchar(10))+'MB'
while (@curSize>@TargetSize) and (@maxTime>0)
begin
backup log @DatabaseName with no_log
DBCC SHRINKFILE(@LogName,@TargetSize)
set @curSize=(select size from sysfiles where name=@LogName)/128
set @maxTime=@maxTime-1
end
set @curSize=(select size from sysfiles where name=@LogName)/128
print '收缩之后的日志文件的大小是:'+cast(@curSize as varchar(10))+'MB'
end
*/
http://download.csdn.net/source/668200
-- 1. 表结构信息查询
-- ========================================================================
-- 表结构信息查询
-- 邹建 2005.08(引用请保留此信息)
-- ========================================================================
SELECT
TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
[Default]=ISNULL(D.definition,N''),
ColumnDesc=ISNULL(PFD.[value],N''),
IndexName=ISNULL(IDX.IndexName,N''),
IndexSort=ISNULL(IDX.Sort,N''),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)
LEFT JOIN -- 索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id
-- WHERE O.name=N'要查询的表' -- 如果只查询指定表,加上此条件
ORDER BY O.name,C.column_id -- 2. 索引及主键信息
-- ========================================================================
-- 索引及主键信息
-- 邹建 2005.08(引用请保留此信息)
-- ========================================================================
SELECT
TableId=O.[object_id],
TableName=O.Name,
IndexId=ISNULL(KC.[object_id],IDX.index_id),
IndexName=IDX.Name,
IndexType=ISNULL(KC.type_desc,'Index'),
Index_Column_id=IDXC.index_column_id,
ColumnID=C.Column_id,
ColumnName=C.Name,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
[UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END,
Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END,
Disabled=CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END,
Fill_factor=IDX.fill_factor,
Padded=CASE WHEN IDX.is_padded=1 THEN N'√'ELSE N'' END
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN sys.objects O
ON O.[object_id]=IDX.[object_id]
INNER JOIN sys.columns C
ON O.[object_id]=C.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
AND IDXC.Column_id=C.Column_id
-- INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
-- (
-- SELECT [object_id], Column_id, index_id=MIN(index_id)
-- FROM sys.index_columns
-- GROUP BY [object_id], Column_id
-- ) IDXCUQ
-- ON IDXC.[object_id]=IDXCUQ.[object_id]
-- AND IDXC.Column_id=IDXCUQ.Column_id
-- 进行演示操作前, 先备份, 以便可以在演示完成后, 恢复到原始状态
USE master
-- 备份
BACKUP DATABASE AdventureWorks
TO DISK = 'AdventureWorks.bak'
WITH FORMAT---- 恢复
--RESTORE DATABASE AdventureWorks
-- FROM DISK = 'AdventureWorks.bak'
-- WITH REPLACE
GO--=========================================
-- 转换为分区表
--=========================================
USE AdventureWorks
GO-- 1. 创建分区函数
-- a. 适用于存储历史存档记录的分区表的分区函数
DECLARE @dt datetime
SET @dt = '20020101'
CREATE PARTITION FUNCTION PF_HistoryArchive(datetime)
AS RANGE RIGHT
FOR VALUES(
@dt,
DATEADD(Year, 1, @dt))-- b. 适用于存储历史记录的分区表的分区函数
--DECLARE @dt datetime
SET @dt = '20030901'
CREATE PARTITION FUNCTION PF_History(datetime)
AS RANGE RIGHT
FOR VALUES(
@dt,
DATEADD(Month, 1, @dt), DATEADD(Month, 2, @dt), DATEADD(Month, 3, @dt),
DATEADD(Month, 4, @dt), DATEADD(Month, 5, @dt), DATEADD(Month, 6, @dt),
DATEADD(Month, 7, @dt), DATEADD(Month, 8, @dt), DATEADD(Month, 9, @dt),
DATEADD(Month, 10, @dt), DATEADD(Month, 11, @dt), DATEADD(Month, 12, @dt))
GO-- 2. 创建分区架构
-- a. 适用于存储历史存档记录的分区表的分区架构
CREATE PARTITION SCHEME PS_HistoryArchive
AS PARTITION PF_HistoryArchive
TO([PRIMARY], [PRIMARY], [PRIMARY])-- b. 适用于存储历史记录的分区表的分区架构
CREATE PARTITION SCHEME PS_History
AS PARTITION PF_History
TO([PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY])
GO-- 3. 删除索引
-- a. 删除存储历史存档记录的表中的索引
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ProductID
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID-- b. 删除存储历史记录的表中的索引
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ProductID
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
GO-- 4. 转换为分区表
-- a. 将存储历史存档记录的表转换为分区表
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH(
MOVE TO PS_HistoryArchive(TransactionDate))-- b.将存储历史记录的表转换为分区表
ALTER TABLE Production.TransactionHistory
DROP CONSTRAINT PK_TransactionHistory_TransactionID
WITH(
MOVE TO PS_History(TransactionDate))
GO-- 5. 恢复主键
-- a. 恢复存储历史存档记录的分区表的主键
ALTER TABLE Production.TransactionHistoryArchive
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)-- b. 恢复存储历史记录的分区表的主键
ALTER TABLE Production.TransactionHistory
ADD CONSTRAINT PK_TransactionHistory_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)
GO-- 6. 恢复索引
-- a. 恢复存储历史存档记录的分区表的索引
CREATE INDEX IX_TransactionHistoryArchive_ProductID
ON Production.TransactionHistoryArchive(
ProductID)CREATE INDEX IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
ON Production.TransactionHistoryArchive(
ReferenceOrderID,
ReferenceOrderLineID)-- b. 恢复存储历史记录的分区表的索引
CREATE INDEX IX_TransactionHistory_ProductID
ON Production.TransactionHistory(
ProductID)CREATE INDEX IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
ON Production.TransactionHistory(
ReferenceOrderID,
ReferenceOrderLineID)
GO-- 7. 查看分区表的相关信息
SELECT
SchemaName = S.name,
TableName = TB.name,
PartitionScheme = PS.name,
PartitionFunction = PF.name,
PartitionFunctionRangeType = CASE
WHEN boundary_value_on_right = 0 THEN 'LEFT'
ELSE 'RIGHT' END,
PartitionFunctionFanout = PF.fanout,
SchemaID = S.schema_id,
ObjectID = TB.object_id,
PartitionSchemeID = PS.data_space_id,
PartitionFunctionID = PS.function_id
FROM sys.schemas S
INNER JOIN sys.tables TB
ON S.schema_id = TB.schema_id
INNER JOIN sys.indexes IDX
on TB.object_id = IDX.object_id
AND IDX.index_id < 2
INNER JOIN sys.partition_schemes PS
ON PS.data_space_id = IDX.data_space_id
INNER JOIN sys.partition_functions PF
ON PS.function_id = PF.function_id
GO--=========================================
-- 移动分区表数据
--=========================================
-- 1. 为存储历史存档记录的分区表增加分区, 并接受从历史记录分区表移动过来的数据
-- a. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]-- b. 修改分区函数, 增加分区用以接受从历史记录分区表移动过来的数据
DECLARE @dt datetime
SET @dt = '20030901'
ALTER PARTITION FUNCTION PF_HistoryArchive()
SPLIT RANGE(@dt)-- c. 将历史记录表中的过期数据移动到历史存档记录表中
ALTER TABLE Production.TransactionHistory
SWITCH PARTITION 2
TO Production.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(@dt)-- d. 将接受到的数据与原来的分区合并
ALTER PARTITION FUNCTION PF_HistoryArchive()
MERGE RANGE(@dt)
GO-- 2. 将存储历史记录的分区表中不包含数据的分区删除, 并增加新的分区以接受新数据
-- a. 合并不包含数据的分区
DECLARE @dt datetime
SET @dt = '20030901'
ALTER PARTITION FUNCTION PF_History()
MERGE RANGE(@dt)-- b. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_History
NEXT USED [PRIMARY]-- c. 修改分区函数, 增加分区用以接受新数据
SET @dt = '20041001'
ALTER PARTITION FUNCTION PF_History()
SPLIT RANGE(@dt)
GO
--=========================================
-- 清除历史存档记录中的过期数据
--=========================================
-- 1. 创建用于保存过期的历史存档数据的表
CREATE TABLE Production.TransactionHistoryArchive_2001_temp(
TransactionID int NOT NULL,
ProductID int NOT NULL,
ReferenceOrderID int NOT NULL,
ReferenceOrderLineID int NOT NULL
DEFAULT ((0)),
TransactionDate datetime NOT NULL
DEFAULT (GETDATE()),
TransactionType nchar(1) NOT NULL,
Quantity int NOT NULL,
ActualCost money NOT NULL,
ModifiedDate datetime NOT NULL
DEFAULT (GETDATE()),
CONSTRAINT PK_TransactionHistoryArchive_2001_temp_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)
)-- 2. 将数据从历史存档记录分区表移动到第1步创建的表中
ALTER TABLE Production.TransactionHistoryArchive
SWITCH PARTITION 1
TO Production.TransactionHistoryArchive_2001_temp-- 3. 删除不再包含数据的分区
DECLARE @dt datetime
SET @dt = '20020101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
MERGE RANGE(@dt)-- 4. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]-- 5. 修改分区函数, 增加分区用以接受新数据
SET @dt = '20040101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
SPLIT RANGE(@dt)
TBINFO AS(
SELECT
SchemaName = S.name,
TableName = TB.name,
PartitionScheme = PS.name,
PartitionFunction = PF.name,
PartitionFunctionRangeType = CASE
WHEN boundary_value_on_right = 0 THEN 'LEFT'
ELSE 'RIGHT' END,
PartitionFunctionFanout = PF.fanout,
SchemaID = S.schema_id,
ObjectID = TB.object_id,
PartitionSchemeID = PS.data_space_id,
PartitionFunctionID = PS.function_id
FROM sys.schemas S
INNER JOIN sys.tables TB
ON S.schema_id = TB.schema_id
INNER JOIN sys.indexes IDX
on TB.object_id = IDX.object_id
AND IDX.index_id < 2
INNER JOIN sys.partition_schemes PS
ON PS.data_space_id = IDX.data_space_id
INNER JOIN sys.partition_functions PF
ON PS.function_id = PF.function_id
),
PF1 AS(
SELECT
PFP.function_id, PFR.boundary_id, PFR.value,
Type = CONVERT(sysname,
CASE T.name
WHEN 'numeric' THEN 'decimal'
WHEN 'real' THEN 'float'
ELSE T.name END
+ CASE
WHEN T.name IN('decimal', 'numeric')
THEN QUOTENAME(RTRIM(PFP.precision)
+ CASE WHEN PFP.scale > 0 THEN ',' + RTRIM(PFP.scale) ELSE '' END, '()')
WHEN T.name IN('float', 'real')
THEN QUOTENAME(PFP.precision, '()')
WHEN T.name LIKE 'n%char'
THEN QUOTENAME(PFP.max_length / 2, '()')
WHEN T.name LIKE '%char' OR T.name LIKE '%binary'
THEN QUOTENAME(PFP.max_length, '()')
ELSE '' END)
FROM sys.partition_parameters PFP
LEFT JOIN sys.partition_range_values PFR
ON PFR.function_id = PFP.function_id
AND PFR.parameter_id = PFP.parameter_id
INNER JOIN sys.types T
ON PFP.system_type_id = T.system_type_id
),
PF2 AS(
SELECT * FROM PF1
UNION ALL
SELECT
function_id, boundary_id = boundary_id - 1, value, type
FROM PF1
WHERE boundary_id = 1
),
PF AS(
SELECT
B.function_id, boundary_id = ISNULL(B.boundary_id + 1, 1),
value = STUFF(
CASE
WHEN A.boundary_id IS NULL THEN ''
ELSE ' AND [partition_column_name] ' + PF.LessThan + ' ' + CONVERT(varchar(max), A.value) END
+ CASE
WHEN A.boundary_id = 1 THEN ''
ELSE ' AND [partition_column_name] ' + PF.MoreThan + ' ' + CONVERT(varchar(max), B.value) END,
1, 5, ''),
B.Type
FROM PF1 A
RIGHT JOIN PF2 B
ON A.function_id = B.function_id
AND (A.boundary_id - 1 = B.boundary_id
OR(A.boundary_id IS NULL AND B.boundary_id IS NULL))
INNER JOIN(
SELECT
function_id,
LessThan = CASE
WHEN boundary_value_on_right = 0 THEN '<='
ELSE '<' END,
MoreThan = CASE
WHEN boundary_value_on_right = 0 THEN '>'
ELSE '>=' END
FROM sys.partition_functions
)PF
ON B.function_id = PF.function_id
),
PS AS(
SELECT
DDS.partition_scheme_id, DDS.destination_id,
FileGroupName = FG.name, IsReadOnly = FG.is_read_only
FROM sys.destination_data_spaces DDS
INNER JOIN sys.filegroups FG
ON DDS.data_space_id = FG.data_space_id
),
PINFO AS(
SELECT
RowID = ROW_NUMBER() OVER(ORDER BY SchemaID, ObjectID, PS.destination_id),
TB.SchemaName, TB.TableName,
TB.PartitionScheme, PS.destination_id, PS.FileGroupName, PS.IsReadOnly,
TB.PartitionFunction, TB.PartitionFunctionRangeType, TB.PartitionFunctionFanout,
PF.boundary_id, PF.Type, PF.value
FROM TBINFO TB
INNER JOIN PS
ON TB.PartitionSchemeID = PS.partition_scheme_id
LEFT JOIN PF
ON TB.PartitionFunctionID = PF.function_id
AND PS.destination_id = PF.boundary_id
)
SELECT
RowID,
SchemaName = CASE destination_id
WHEN 1 THEN SchemaName
ELSE N'' END,
TableName = CASE destination_id
WHEN 1 THEN TableName
ELSE N'' END,
PartitionScheme = CASE destination_id
WHEN 1 THEN PartitionScheme
ELSE N'' END,
destination_id, FileGroupName, IsReadOnly,
PartitionFunction = CASE destination_id
WHEN 1 THEN PartitionFunction
ELSE N'' END,
PartitionFunctionRangeType = CASE destination_id
WHEN 1 THEN PartitionFunctionRangeType
ELSE N'' END,
PartitionFunctionFanout = CASE destination_id
WHEN 1 THEN CONVERT(varchar(20), PartitionFunctionFanout)
ELSE N'' END,
boundary_id = ISNULL(CONVERT(varchar(20), boundary_id), ''),
Type = ISNULL(Type, N''),
value = CASE PartitionFunctionFanout
WHEN 1 THEN '<ALL Data>'
ELSE ISNULL(value, N'<NEXT USED>') END
FROM PINFO
ORDER BY RowID