2019-1-11存储过程的查看

1.查询所有存储过程

select Pr_Name as [存储过程], [参数]=stuff((select '，'+[Parameter]
from (
select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
from sys.procedures Pr left join
sys.parameters parameter on Pr.object_id = parameter.object_id
inner join sys.types Type on parameter.system_type_id = Type.system_type_id
where type = 'P'
) t where Pr_Name=tb.Pr_Name for xml path('')), 1, 1, '')
from (
select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
from sys.procedures Pr left join
sys.parameters parameter on Pr.object_id = parameter.object_id
inner join sys.types Type on parameter.system_type_id = Type.system_type_id
where type = 'P'
)tb
where Pr_Name not like 'sp_%' --and Pr_Name not like 'dt%'
group by Pr_Name
order by Pr_Name

　　2. 存储过程信息查询

select Pr.Name as Pr_Name,parameter.name,T.Name,convert(varchar(32),parameter.max_length) as 参数长度,parameter.is_output as 是否是输出参数,parameter.*
from sys.procedures Pr left join
sys.parameters parameter on Pr.object_id = parameter.object_id
inner join sys.types T on parameter.system_type_id = T.system_type_id
where Pr.type = 'P' and Pr.Name like 'order_%' and T.name!='sysname' order by Pr.Name

　　3. 显示存储过程内容

SELECT TEXT FROM syscomments WHERE id=object_id('SP_NAME')
  
SP_HELPTEXT 'SP_NAME'

　　4. 查询所有触发器

select triggers.name as [触发器],tables.name as [表名],triggers.is_disabled as [是否禁用],
triggers.is_instead_of_trigger AS [触发器类型],
case when triggers.is_instead_of_trigger = 1 then 'INSTEAD OF'
when triggers.is_instead_of_trigger = 0 then 'AFTER'
else null
end as [触发器类型描述]
from sys.triggers triggers
inner join sys.tables tables on triggers.parent_id = tables.object_id
where triggers.type ='TR'
order by triggers.create_date

　　5. 查询所有索引

select indexs.Tab_Name as [表名],indexs.Index_Name as [索引名] ,indexs.[Co_Names] as [索引列],
Ind_Attribute.is_primary_key as [是否主键],Ind_Attribute.is_unique AS [是否唯一键],
Ind_Attribute.is_disabled AS [是否禁用]
from (
select Tab_Name,Index_Name, [Co_Names]=stuff((select '，'+[Co_Name] from
( select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind
inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
) t where Tab_Name=tb.Tab_Name and Index_Name=tb.Index_Name for xml path('')), 1, 1, '')
from (
select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind
inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
)tb
where Tab_Name not like 'sys%'
group by Tab_Name,Index_Name
) indexs inner join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.name
order by indexs.Tab_Name

　　sql server性能分析--检测数据库阻塞语句

100

create PROCEDURE [dbo].[auto_checkblocks] AS
set nocount on
if exists ( select * from master..sysprocesses where blocked <> 0 )
 
begin
    /* show top blockers, but no duplicates */
    select '请尝试使用KILL [SPID] 来杀进程'
  --      select '请尝试使用SP_LOCK [SPID]来显示锁信息，用OBJECT_NAME(ID)来显示锁对象名称或用sp_who [SPID] 来显示信息'
   --     select '在使用OBJECT_NAME显示对象名称时请注意对应的db_id' 
       select '以下是引起阻塞的语句'
    select distinct
    '进程ID'              = str( a.spid, 4 ),
    '进程ID状态'          = convert( char(20), a.status ),
    '分块进程的进程ID'    = str( a.blocked, 2 ),
    '工作站名称'          = convert( char(40), a.hostname ),
    '执行命令的用户'      = convert( char(20), suser_name( a.uid ) ),
    '数据库名'            = convert( char(20), db_name(a.dbid ) ),       
    '应用程序名'          = convert( char(30), a.program_name ),
    '正在执行的命令'      = convert( char(26), a.cmd ),
    '累计CPU时间'         = str( a.cpu, 7 ),
    'IO'                  = str( a.physical_io, 7 ),
        '登录名'              = a.loginame,
    '执行语句'=b.text
    from master..sysprocesses a
    cross apply sys.dm_exec_sql_text(a.sql_handle) b
    where spid in ( select blocked from master..sysprocesses )
    and blocked = 0
    order by str(spid,4)
 
    /* 显示阻塞牺牲品 */
    select '以下是被阻塞的等待执行的语句'
        select
    '进程ID[SPID]'        = str( a.spid, 4 ),
    '进程ID状态'          = convert( char(20), a.status ),
    '分块进程的进程ID'    = str( a.blocked, 2 ),
    '工作站名称'          = convert( char(40), a.hostname ),
    '执行命令的用户'      = convert( char(10), suser_name( a.uid ) ),
    '数据库名'            = convert( char(10), db_name( a.dbid ) ),       
    '应用程序名'          = convert( char(20), a.program_name ),
    '正在执行的命令'      = convert( char(16), a.cmd ),
    '累计CPU时间'         = str( a.cpu, 7 ),
    'IO'                  = str( a.physical_io, 7 ),
        '登录名'              = a.loginame,
    '执行语句'=b.text
    from master..sysprocesses a
    cross apply sys.dm_exec_sql_text(a.sql_handle) b
    where blocked <> 0
    order by spid
end
 
else
begin
    select '恭喜！当前没有阻塞，当前的进程信息如下.', convert (char(24),getdate(),13)
        select
    '进程ID'              = str( spid, 4 ),
    '进程ID状态'          = convert( char(20), status ),
    '分块进程的进程ID'    = str( blocked, 2 ),
    '工作站名称'          = convert( char(40), hostname ),
    '执行命令的用户'      = convert( char(10), suser_name( uid ) ),
    '数据库名'            = convert( char(10), db_name( dbid ) ),       
    '应用程序名'          = convert( char(20), program_name ),
    '正在执行的命令'      = convert( char(26), cmd ),
    '累计CPU时间'         = str( cpu, 7 ),
    'IO'                  = str( physical_io, 7 ),
        '登录名'              = loginame
    from master..sysprocesses
    where blocked = 0
    order by spid
end
 
return
 
 
 
----查询存储过程
select * from sysobjects where type='P'
 --查询存储过程语句、红色部分是“存储过程”名称
 select text from syscomments where id=object_id('auto_checkblocks') 
--查询存储过程
select * from sys .procedures
--查询触发器
select * from sysobjects where xtype='TR'
--查询本地表
select * from sysobjects where xtype='U'
--调用存储过程
EXEC auto_checkblocks
--删除存储过程
drop procedure auto_checkblocks
--Sql Server查看所有存储过程或视图的位置及内容
select a.name,a.[type],b.[definition] from sys.all_objects a,sys.sql_modules b
where a.is_ms_shipped=0 and a.object_id = b.object_id and a.[type] in ('P','V','AF')
order by a.[name] asc
--该视图 很出名，是经常使用到的，主要是：显示所有架构范围内的用户定义对象和系统对象的 UNION
select * from sys.objects
--Sql Server 查看存储过程在哪些作业中被调用
SELECT * 
FROM msdb.dbo.sysjobs JOB WITH( NOLOCK) 
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK ) 
ON STP .job_id = JOB .job_id 
WHERE STP .command LIKE N’% sp_name %’