SQL Server适用脚本收集一

1.IO分析

with DB_IO as (
select
    db_name(a.database_id) as [dbname],
    case when b.type_desc='LOG' then 'LogFile' else 'DataFile' end as FileType,
    num_of_reads,
    num_of_writes,
    sum(a.io_stall) as io_stall,
    sum(num_of_bytes_read+num_of_bytes_written) as TotalIOBytes
from sys.dm_io_virtual_file_stats(null,null) a join sys.master_files b
on a.database_id=b.database_id and a.file_id=b.file_id
group by db_name(a.database_id),b.type_desc,num_of_reads,num_of_writes
)
select
    dbname,
    FileType,
    num_of_reads,
    num_of_writes,
    cast(1. * TotalIOBytes/(1024*1024) as decimal(12,2)) as IO_MB,
    cast(1. * io_stall/1000 as decimal(12,2)) as IO_stall_s,
    cast(100. * io_stall/sum(io_stall) over() as decimal(10,2)) as [IO_stall_pct%],--占总IO的百分比
    row_number() over(order by io_stall desc) as rn
from DB_IO

 

2.表结构

DECLARE @TableName varchar(20)
SET @TableName='HR_Duty'
SELECT
        (CASE when a.colorder=1 then d.name else '' end) AS 表名,
        a.colorder 字段序号,
        a.name 字段名,
        (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
        (case when (SELECT count(*)
        FROM sysobjects
        WHERE (name in
                  (SELECT name
                FROM sysindexes
                WHERE (id = a.id) AND (indid in
                          (SELECT indid
                         FROM sysindexkeys
                         WHERE (id = a.id) AND (colid in
                                   (SELECT colid
                                  FROM syscolumns
                                  WHERE (id = a.id) AND (name = a.name))))))) AND
              (xtype = 'PK'))>0 then '√' else '' end) 主键,
       b.name 类型,
       a.length 占用字节数,
       COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
       isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
       (case when a.isnullable=1 then '√'else '' end) 允许空,
       isnull(e.text,'') 默认值,
       isnull(g.[value],'') AS 字段说明

FROM syscolumns  a
    LEFT JOIN systypes b on  a.xtype=b.xusertype
    inner join sysobjects d on a.id=d.id  and  d.xtype='U' and  d.name<>'dtproperties'
    left join syscomments e on a.cdefault=e.id
    left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id
WHERE d.name=@TableName    --如果只查询指定表,加上此条件
order by a.id,a.colorder

SELECT * FROM sys.extended_properties

 

3.查进程及CPU占用时间,text为运行的Sql

select db_name(database_id),session_id,cpu_time,start_time,command,database_id,[user_id],connection_id,[text]
from sys.dm_exec_requests t1
cross apply sys.dm_exec_sql_text(t1.sql_handle)
--WHERE session_id=@@spid
ORDER BY cpu_time desc
--杀死相关进程
SELECT @@spid
--KILL 2529

--sELECT text FROM sys.dm_exec_requests as t1
--cross apply sys.dm_exec_sql_text(t1.sql_handle)
--,sys.dm_os_tasks t2,sys.dm_os_threads t3
--where t1.session_id=t2.session_id and t2.worker_address=t3.worker_address
--and  t3.os_thread_id=24564.查找对象依赖项--------------------------------------------------------------------------------
--查找对象依赖项
--所有依赖此对象的 过程，函数，触发器，视图
------------------------------------------------------------------------------
declare @obj_name nvarchar(128)
select @obj_name='C_Users'--'GetCSWC'
--select @obj_name='C_Depts'--'GetCSWC'
create table #obj_depends
(
    [obj_name] nvarchar(128),
    [obj_type] char(2)
)

insert into #obj_depends
select o.[name],o.type
from sys.syscomments c
    left join sys.objects o on c.id=o.[object_id]
where c.text like '%'+@obj_name+'%' and o.[name]<>@obj_name

if exists(select 1 from sys.syscomments where object_name(id)<>@obj_name group by id having count(*)>1) begin
    declare @objid int
    declare cur_comments cursor for select id from sys.syscomments where object_name(id)<>@obj_name group by id having count(*)>1
    open cur_comments
    fetch cur_comments into @objid
    while @@fetch_status=0 begin
        declare @text nvarchar(4000)
        select @text=''
        select @text=@text+left(ltrim([text]),128)+'...'+right(rtrim([text]),128)
        from sys.syscomments where id=@objid order by colid
        if @text like '%'+@obj_name+'%' begin
            if not exists(select 1 from #obj_depends where obj_name=object_name(@objid)) begin
                insert into #obj_depends
                select top 1 o.[name],o.type
                from sys.syscomments c
                    left join sys.objects o on c.id=o.[object_id]
                where c.id=@objid
            end
        end
        fetch next from cur_comments into @objid
    end
    close cur_comments
    deallocate cur_comments
end

select distinct * from #obj_depends order by [obj_type],[obj_name]
drop table #obj_depends

4.根据SQL存储过程名取得存储过程的内容

--     GetContentByProcedureName '[dbo].[存储过程名]'
--Create procedure GetContentByProcedureName
--(@ProcedureName nvarchar(500))
--as
DECLARE @ProcedureName nvarchar(500)
SELECT @ProcedureName='B_Add_Proc'
if exists (select * from dbo.syscomments where id=object_id(N''+@ProcedureName+''))
    select c.text, c.encrypted, c.number,
    xtype=convert(nchar(2), o.xtype),    
    datalength(c.text), convert(varbinary(8000),
    c.text), 0 from dbo.syscomments c, dbo.sysobjects o   
    where o.id = c.id and c.id = object_id(N''+@ProcedureName+'')
    order by c.number, c.colid option(robust plan)