REDGATE又一好用的脚本工具ScriptsManager1.3

REDGATE又一好用的脚本工具ScriptsManager1.3 先说明一下：这个工具是免费的 下载地址： 这个工具是常用脚本工具，里面已经集合了SQLSERVER砖家们的脚本，这些脚本涉及很多方面 包括：备份，诊断，导出，索引，空间，模版，工具 大家可以调用这些脚本，不用

REDGATE又一好用的脚本工具ScriptsManager1.3

先说明一下：这个工具是免费的

下载地址：

这个工具是常用脚本工具，里面已经集合了SQLSERVER砖家们的脚本，这些脚本涉及很多方面

包括：备份，诊断，，导出，索引，空间，模版，工具

大家可以调用这些脚本，不用再打开你的脚本文件夹到处搜索你的笔记了

选择好脚本之后就打开

打开之后会显示脚本的用途

脚本代码

贡献脚本的作者信息

这些作者都来自知名数据库网站

点击“Continue”

选择在哪个数据库上运行这个脚本

点击“run”运行

运行结果

也可以将结果导出csv文件

也可以贡献您的代码到社区

 

工具里面的所有脚本代码

 

脚本大全 redgate的ScriptsManager sd.name , bs.type bs.type bs.type bs.type bs.type bs.type bs.type , bs.backup_start_date master..sysdatabases sd msdb..backupset bs (sd.name) msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id bs.backup_start_date sd.name , --, bs.type bs.type bs.type bs.type bs.type bs.type bs.type bs.type , master..sysdatabases sd msdb..backupset bs (sd.name) msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id sd.name, bs.type, bs.database_name --nocount #Data( FileID , , TotalExtents , UsedExtents , sysname NOT NULL, (MAX) NOT NULL, (MAX) NULL) create table #Results( db sysname NULL , FileType varchar(4) NOT NULL, sysname not null, sysname NOT NULL, TotalMB numeric(18,2) NOT NULL, UsedMB numeric(18,2) NOT NULL, PctUsed numeric(18,2) NULL, FilePath nvarchar(MAX) NULL, FileID int null) create table #Log( db sysname NOT NULL, LogSize numeric(18,5) NOT NULL, LogUsed numeric(18,5) NOT NULL, Status , (MAX) NULL) , TotalExtents, UsedExtents, , ) ) update #Data set #Data.FileGroup = sysfilegroups.groupname from #Data, sysfilegroups where #Data.FileGroupId = sysfilegroups.groupid , FileType, , TotalMB, UsedMB, PctUsed, FilePath, FileID) SELECT DB_NAME() db, , FileType, , TotalExtents . TotalMB, UsedExtents UsedMB, UsedExtents*100. /TotalExtents UsedPct, , FileID FROM #Data (), #Log (db,LogSize,LogUsed,Status) ) , FileType, , TotalMB,UsedMB, PctUsed, FilePath, FileID) select DB_NAME() db, , FileType, s., s.Size/128. as LogSize , ) LogUsedSpace, (()).) UsedPct, s.FileName FilePath, s.FileID FileID from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s where f.dbid = DB_ID() s.FileID = f.FileID and l.db = DB_NAME() SELECT r.db AS "Database", r.FileType AS "File type", r.FileGroup END "File group", r.FileName AS "Logical file name", r.TotalMB AS "Total size (MB)", r.UsedMB AS "Used (MB)", r.PctUsed AS "Used (%)", r.FilePath AS "File name", r.FileID AS "File ID", (decimal(18,2), s.maxsize /128.) END "Max. size (MB)", CONVERT(decimal(18,2), s.growth /128.) "Autogrowth increment (MB)" FROM #Results r INNER JOIN dbo.sysfiles s ON r.FileID = s.FileID , #Data DROP TABLE #Results --t2.cntr_value ((t2.cntr_value ) sys.dm_os_performance_counters t1, sys.dm_os_performance_counters t2 WHERE t1.t1.t1.counter_namet2.counter_name --(ST., ((CASE statement_end_offset (ST.text) ELSE QS.statement_end_offset ) + 1) AS "Statement Text", total_worker_time "Average Worker Time (ms)", execution_count AS "Execution Count", total_worker_time "Total Worker Time (ms)", total_logical_reads AS "Total Logical Reads", total_logical_reads/execution_count AS "Average Logical Reads", total_elapsed_time "Total Elapsed Time (ms)", total_elapsed_time "Average Elapsed Time (ms)", QP.query_plan AS "Query Plan (double click to open)" FROM sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP --() OBJECT_SCHEMA_NAME(ddips., (ddips., , i. , ddips., ddips., ddips., ddips., ddips., ) , ) , ddips., ddips.sys.dm_db_index_physical_stats(DB_ID(), NULL, ) ddips i i.ddips.i.ddips.ddips.ddips. , , DB_ID()) , i. -- , , dopc_fraction., dopc_fraction.instance_name , dopc_fraction.counter_name , (DECIMAL(38,2), CAST(dopc_fraction.cntr_value AS FLOAT) / CAST(CASE dopc_base.cntr_value dopc_base.cntr_value )) sys.dm_os_performance_counters AS dopc_base JOIN sys.dm_os_performance_counters AS dopc_fraction dopc_fraction.cntr_type dopc_base.dopc_base.instance_name = dopc_fraction.instance_name , '') = UPPER(dopc_fraction.counter_name) (, , '') ) ORDER BY dopc_fraction.object_name , dopc_fraction.instance_name , dopc_fraction.counter_name ----------------------------------------------------------------------- -- o.name FROM sys.objects o ( sys.indexes i i.type_desc ) --------------------------------------------------------------------------- --(DTL.) , DTL. , ( , , ) DTL.(DTL.resource_associated_entity_id) ( , , ) ) FROM sys.partitions DTL. ) , DTL. , DTL. , DOWT. , DOWT. , DOWT. , DES_Blocked. , , ( (DEST_Blocked.text) ELSE DER.statement_end_offset ) , DOWT. , DES_Blocking. , DEST_Blocking. , DOWT.resource_description sys.dm_tran_locks DTL INNER JOIN sys.dm_os_waiting_tasks DOWT ON DTL.lock_owner_address = DOWT.resource_address DER DER. sys.dm_exec_sessions DES_Blocked DES_Blocked. sys.dm_exec_sessions DES_Blocking DES_Blocking.sys.dm_exec_connections .APPLY sys.dm_exec_sql_text() AS DEST_Blocking CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked ------------------------------------------------------------------------------ --(10) RANK() , , execution_count , , SUBSTRING(execText.text, deqs.statement_start_offset deqs.statement_start_offset deqs.statement_start_offset, deqs.statement_end_offset deqs.statement_end_offset deqs.statement_end_offset (execText.text) deqs.statement_start_offset deqs.statement_start_offset deqs.statement_start_offset ) , execText. sys.dm_exec_query_stats deqs CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText ORDER BY deqs.total_worker_time DESC ; ------------------------------------------------------------------------------------ --(database_id) , , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) , io_stall_write_ms , num_of_writes , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) , io_stall_read_ms , num_of_reads , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) sys.dm_io_virtual_file_stats( ; --FROM sys.dm_io_virtual_file_stats(DB_ID('AdventureWorks'), NULL) --------------------------------------------------------------------------- --OBJECT_SCHEMA_NAME(ddius.(ddius. , ) (DECIMAL(38,2), CAST(SUM(user_seeks + user_scans + user_lookups) AS DECIMAL) / CAST(SUM(user_updates + user_seeks + user_scans + user_lookups) AS DECIMAL) ) , ) (DECIMAL(38,2), CAST(SUM(user_updates) AS DECIMAL) / CAST(SUM(user_updates + user_seeks + user_scans + user_lookups) AS DECIMAL) ) , , sys.dm_db_index_usage_stats AS ddius ddius.index_id = i.index_id , ) ddius.OBJECT_SCHEMA_NAME(ddius.(ddius.object_id) ------------------------------------------------------------------------------ --列出服务器实例级别下的最长的资源等待，帮你找出系统瓶颈 WITH Waits AS ( SELECT wait_type , wait_time_ms / 1000. AS wait_time_sec , 100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct , ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn FROM sys.dm_os_wait_stats , , , , , , , , ) ) , , Waits wait_time_sec --, der.command , dest. , des.login_time , des., des., der.session_id , , der.status , sys.dm_exec_requests der der.session_id = dec.session_id INNER JOIN sys.dm_exec_sessions des ON des.session_id = der.session_id CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest --(60) (1400) name from msdb.sys.sysdatabases CREATE TABLE #parts (dbname VARCHAR(60), part int) OPEN getdb getdb #parts VALUES(@db, 0) .sys.sysobjects so where sp.object_id = so.id and partition_number != 1 and #parts.dbname = (@vsql) getdb getdb , part #parts drop table #parts deallocate getdb Partitioned Tables ------------------------------------------------------------ ---------------------------- master 0 tempdb 0 model 0 msdb 0 DLGPOS 0 GPOSDB 0 Northwind 0 partionTest --(2000) #TempLog ( LogDate DATETIME, ProcessInfo NVARCHAR(50), (MAX)) CREATE TABLE #logF ( ArchiveNumber INT, LogDate DATETIME, LogSize INT ) INSERT INTO #logF EXEC sp_enumerrorlogs (ArchiveNumber) FROM #logF #TempLog (ArchiveNumber) FROM #logF (, Text AS Details FROM #TempLog (, Text AS Details FROM #TempLog #TempLog DROP TABLE #logF ------------------------------------------------------------------------- --()), , , master.dbo.sysdatabases master.dbo.sysdatabases