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 sdmsdb..backupset bs (sd.name)msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_idbs.backup_start_date sd.name ,—,bs.type bs.type bs.type bs.type bs.type bs.type bs.type bs.type , master..sysdatabases sdmsdb..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 #Dataset #Data.FileGroup = sysfilegroups.groupnamefrom #Data, sysfilegroupswhere #Data.FileGroupId = sysfilegroups.groupid, FileType, , TotalMB, UsedMB, PctUsed, FilePath, FileID)SELECT DB_NAME() db,, FileType,,TotalExtents . TotalMB,UsedExtents UsedMB,UsedExtents*100. /TotalExtents UsedPct,,FileIDFROM #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 FileIDfrom #Log l , master.dbo.sysaltfiles f , dbo.sysfiles swhere f.dbid = DB_ID()s.FileID = f.FileIDand l.db = DB_NAME()SELECT r.db AS “Database“,r.FileType AS “File type”, r.FileGroupEND “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 rINNER JOIN dbo.sysfiles sON r.FileID = s.FileID, #DataDROP TABLE #Results—t2.cntr_value ((t2.cntr_value ) sys.dm_os_performance_counters t1,sys.dm_os_performance_counters t2WHEREt1.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 QSCROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) STCROSS 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,) ddipsi 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_baseJOIN sys.dm_os_performance_counters AS dopc_fractiondopc_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.nameFROM sys.objects o( sys.indexes ii.type_desc )—————————————————————————-—(DTL.) ,DTL. ,( , , )DTL.(DTL.resource_associated_entity_id)( , , ))FROM sys.partitionsDTL.) ,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 DTLINNER JOIN sys.dm_os_waiting_tasks DOWTON DTL.lock_owner_address = DOWT.resource_address DERDER. sys.dm_exec_sessions DES_BlockedDES_Blocked. sys.dm_exec_sessions DES_BlockingDES_Blocking.sys.dm_exec_connections .APPLY sys.dm_exec_sql_text()AS DEST_BlockingCROSS 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 deqsCROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execTextORDER 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(us本文来源[email protected]搞@^&代*@码2网er_updates) AS DECIMAL)/ CAST(SUM(user_updates + user_seeks + user_scans+ user_lookups) AS DECIMAL) ) , ,sys.dm_db_index_usage_stats AS ddiusddius.index_id = i.index_id, ) ddius.OBJECT_SCHEMA_NAME(ddius.(ddius.object_id)——————————————————————————-—列出服务器实例级别下的最长的资源等待，帮你找出系统瓶颈WITH WaitsAS ( 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 rnFROMsys.dm_os_wait_stats, ,, ,,, ,, )), , Waitswait_time_sec —,der.command ,dest. ,des.login_time ,des.,des.,der.session_id ,,der.status , sys.dm_exec_requests derder.session_id = dec.session_idINNER JOIN sys.dm_exec_sessions desON des.session_id = der.session_idCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest—(60)(1400)name from msdb.sys.sysdatabasesCREATE TABLE #parts (dbname VARCHAR(60), part int)OPEN getdb getdb#parts VALUES(@db, 0).sys.sysobjects sowhere sp.object_id = so.id and partition_number != 1 and #parts.dbname = (@vsql)getdb getdb, part #partsdrop table #partsdeallocate getdb Partitioned Tables————————————————————- —————————-master0tempdb0model0msdb0DLGPOS0GPOSDB0Northwind0partionTest—(2000) #TempLog (LogDateDATETIME,ProcessInfo NVARCHAR(50),(MAX))CREATE TABLE #logF (ArchiveNumberINT,LogDateDATETIME,LogSizeINT)INSERT INTO #logF EXEC sp_enumerrorlogs(ArchiveNumber) FROM #logF #TempLog(ArchiveNumber) FROM #logF(, Text AS DetailsFROM #TempLog(, Text AS DetailsFROM #TempLog #TempLogDROP TABLE #logF————————————————————————–—()), , , master.dbo.sysdatabasesmaster.dbo.sysdatabases

喜欢 (0)赏

[搞代码]

分享 (0)