欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-04-26 18:30:10
...

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

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

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

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

下载地址:

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

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

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

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

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

选择好脚本之后就打开

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

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

脚本代码

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

贡献脚本的作者信息

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

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

点击“Continue”

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

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

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

点击“run”运行

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

运行结果

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

也可以将结果导出csv文件

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

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

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

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

工具里面的所有脚本代码

脚本大全 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