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
GO

--1、查看所有存储过程与函数 
exec sp_stored_procedures 
--或者 
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name 
--2、查看存储过程的内容    
select text from syscomments where id=object_id('dbo.CalcCustomerPrice') 
-- 或者用 
sys.sp_helptext  dbo.CalcCustomerPrice
GO


---
sys.sp_helptext @objname = N'CalcCustomerPrice', -- nvarchar(776)
    @columnname = NULL -- sysname

sys.sp_helptext dbo.CalcCustomerPrice
GO

sys.sp_helptext @objname = N'', -- nvarchar(776)
    @columnname = NULL -- sysname


--3、查看存储过程的参数情况 
   select '参数名称' = name, 
         '类型' = type_name(xusertype), 
         '长度' = length,    
         '参数顺序' = colid, 
         '排序方式' = collation 
   from    syscolumns 
   where   id=object_id('CalcCustomerPrice')
   
   
--4、查看所有存储过程内容 
select   b.name   ,a.text   from   syscomments   a,sysobjects   b   where   object_id(b.name)=a.id   and   b.xtype   in('P','TR') 

--5、查看包含字符串内容的存储过程 
select   b.name   ,a.text   from   syscomments   a,sysobjects   b 
where 
charindex('字符串内容',a.text)>0    and 
object_id(b.name)=a.id   and   b.xtype   in('P','TR')
GO

  --查看存储过程参数信息：   
--如果返回值>1，则有参数。否则无   
CREATE   PROC sp_PROC_Params 
       @procedure_name sysname  ,  --存储过程或者用户定义函数名   
       @group_number int=1     ,   --存储过程的组号,必须在0到32767之间,0表示显示该存储过程组的所有参数   
       @operator nchar(2)=N'='     --查找对象的运算符   
AS 
SET   NOCOUNT ON   
DECLARE @SQL nvarchar(4000)   
SET @SQL=N'SELECT   
  PorcedureName=CASE     
  WHEN   o.xtype   IN(''P'',''X'')   
  THEN   QUOTENAME(o.name)+N'';''+CAST(c.number   as   varchar)   
  WHEN   USER_NAME(o.uid)=''system_function_schema''   
  AND   o.xtype=''FN''   
  THEN   o.name   
  WHEN     USER_NAME(o.uid)=''system_function_schema''   
  THEN   ''::''+o.name   
  WHEN   o.xtype=''FN''   
  THEN   QUOTENAME(USER_NAME(o.uid))+N''.''+QUOTENAME(o.name)   
  ELSE   QUOTENAME(o.name)   END,   
  Owner=USER_NAME(o.uid),   
  GroupNumber=c.number,   
  ParamId=c.colid,   
  ParamName=CASE     
  WHEN   o.xtype=''FN''   AND   c.colid=0   THEN   ''<Returns>''   
  ELSE   c.name   END,   
  Type=QUOTENAME(t.name)+CASE     
  WHEN   t.name   IN   (''decimal'',''numeric'')   
  THEN   N''(''+CAST(c.prec   as   varchar)+N'',''+CAST(c.scale   as   varchar)+N'')''   
  WHEN   t.name=N''float''   
  OR   t.name   like   ''%char''   
  OR   t.name   like   ''%binary''   
  THEN   N''(''+CAST(c.prec   as   varchar)+N'')''   
  ELSE   ''''   END,   
  Orientation=CASE     
  WHEN   o.xtype=''FN''   AND   c.colid=0   THEN   ''<Returns>''   
  ELSE   N''Input''   
  +CASE   WHEN   c.isoutparam=1   THEN   ''/Output''   ELSE   ''''   END   
  END   
  FROM   sysobjects   o,syscolumns   c,systypes   t   
  WHERE   o.id=c.id   
  AND   c.xusertype=t.xusertype   
  AND   o.name' 
    +CASE WHEN @operator IN ('=','>','>=','!>','<','<=','!<','<>','!=') 
          THEN @operator+QUOTENAME(@procedure_name,'''') 
          WHEN @operator='IN' 
          THEN @operator+N'   IN('+QUOTENAME(@procedure_name,'''')+')' 
          WHEN @operator IN ('LIKE','%') 
          THEN '   LIKE   '+QUOTENAME(@procedure_name,'''') 
          ELSE '='+QUOTENAME(@procedure_name,'''') 
     END+N'     
  AND(('+CASE WHEN @group_number BETWEEN 1 AND 32767 
              THEN N'c.number='+CAST(@group_number as varchar) 
              WHEN @group_number=0 THEN N'1=1' 
              ELSE N'c.number=1' 
         END+N'   AND   o.xtype   IN(''P'',''X''))     
  OR   (c.number=0   AND   o.xtype=''FN'')   
  OR   (c.number=1   AND   o.xtype   IN(''IF'',''TF'')))'   
EXEC sp_executesql @SQL  
GO



--返回在 Sql Server 实例中的数据库或可以 通过数据库网关访问的数据库
EXEC sp_databases 
GO

EXEC SYS.sp_databases
GO


--返回可在当前环境中查询的对象列表。也就是说，返回任何能够在 FROM 子句中出现的对象（不包括同义词对象）

EXEC sp_tables @table_owner='dbo'
GO


EXEC sp_tables   
   @table_name = '%',  
   @table_owner = 'Person',  
   @table_qualifier = 'AdventureWorks2012';  
GO
--返回针对指定的表或索引视图的所有索引和统计信息的列表，用于观察表的索引情况，很有用
EXEC sp_statistics 'APClericalCostDetail'
GO

--返回 SQL Server、数据库网关或基础数据源的属性名称和匹配值的列表。
EXEC sys.sp_server_info
GO

EXEC sp_server_info
GO

EXEC SYS.sp_server_info @attribute_id = 0 -- int


--返回当前环境中可查询的指定表或视图的列信息
EXEC sys.sp_columns @table_name = N'', -- nvarchar(384)
    @table_owner = N'', -- nvarchar(384)
    @table_qualifier = NULL, -- sysname
    @column_name = N'', -- nvarchar(384)
    @ODBCVer = 0 -- int
    
 EXEC sp_columns @table_name = N'APClericalCostDetail', 
    @table_owner = N'dbo'
 GO
 
 --返回一组唯一标识表中某个行的最优列。如果事务更新了行中的某个值，则还将返回自动更新的列    
 EXEC sys.sp_special_columns @table_name = NULL, -- sysname
     @table_owner = NULL, -- sysname
     @table_qualifier = NULL, -- sysname
     @col_type = '', -- char(1)
     @scope = '', -- char(1)
     @nullable = '', -- char(1)
     @ODBCVer = 0 -- int
 
 GO
 
EXEC sys.sp_special_columns @table_name = N'APClericalCostDetail', -- sysname
     @table_owner = 'dbo'     
GO

--为当前环境中的单个存储过程或用户定义函数返回列信息
EXEC sys.sp_sproc_columns @procedure_name = N'', -- nvarchar(390)
    @procedure_owner = N'', -- nvarchar(384)
    @procedure_qualifier = NULL, -- sysname
    @column_name = N'', -- nvarchar(384)
    @ODBCVer = 0, -- int
    @fUsePattern = NULL -- bit
GO

EXEC sys.sp_sproc_columns @procedure_name = N'CalcVendorPrice', -- nvarchar(390)
    @procedure_owner = N'dbo'
GO

--返回指定的一个或多个表的表权限的列表，即 返回当前用户的 对该表的 INSERT、DELETE、UPDATE、SELECT、REFERENCES 权限，并且必须有该数据库的访问权。
EXEC sys.sp_table_privileges @table_name = N'', -- nvarchar(384)
    @table_owner = N'', -- nvarchar(384)
    @table_qualifier = NULL, -- sysname
    @fUsePattern = NULL -- bit
GO

EXEC sys.sp_table_privileges @table_name = N'APClericalCostDetail', -- nvarchar(384)
    @table_owner = N'dbo'
GO

--返回当前环境中单个表的列特权信息。
sp_column_privileges

--返回当前环境中的存储过程列表
sp_stored_procedures 

--返回当前表中的主键信息 sp_pkeys 
EXEC sys.sp_pkeys @table_name = NULL, -- sysname
    @table_owner = NULL, -- sysname
    @table_qualifier = NULL -- sysname
GO
 EXEC  sys.sp_pkeys @table_name = 'APClericalCostDetail', -- sysname
    @table_owner = N'dbo'
    GO
 --返回当前表中的外键信息    
 EXEC sys.sp_fkeys @pktable_name = NULL, -- sysname
     @pktable_owner = NULL, -- sysname
     @pktable_qualifier = NULL, -- sysname
     @fktable_name = NULL, -- sysname
     @fktable_owner = NULL, -- sysname
     @fktable_qualifier = NULL -- sysname
GO

EXEC sys.sp_fkeys @pktable_name = 'APClericalCostDetail', -- sysname
     @pktable_owner = N'dbo'
go
  
--Sys.SysProcesses 系统表是一个很重要的系统视图，主要用来定位与解决Sql Server的阻塞和死锁

/*
视图中主要的字段：
1. Spid：Sql Servr 会话ID
2. Kpid：Windows  线程ID
3. Blocked：正在阻塞求情的会话 ID。如果此列为 Null，则标识请求未被阻塞
4. Waittype：当前连接的等待资源编号，标示是否等待资源，0 或 Null表示不需要等待任何资源
5. Waittime：当前等待时间，单位为毫秒，0 表示没有等待
6. DBID：当前正由进程使用的数据库ID
7. UID：执行命令的用户ID
8. Login_time：客户端进程登录到服务器的时间。
9. Last_batch：上次执行存储过程或Execute语句的时间。对于系统进程，将存储Sql Server 的启动时间
10.Open_tran：进程的打开事务个数。如果有嵌套事务，就会大于1
11.Status：进程ID 状态，dormant = 正在重置回话 ; running = 回话正在运行一个或多个批处理 ; background = 回话正在运行一个后台任务 ; rollback = 会话正在处理事务回滚 ; pending = 回话正在等待工作现成变为可用 ; runnable = 会话中的任务在等待获取 Scheduler 来运行的可执行队列中 ; spinloop = 会话中的任务正在等待自旋锁变为可用 ; suspended = 会话正在等待事件完成
12.Hostname：建立链接的客户端工作站的名称
13.Program_name：应用程序的名称，就是 连接字符串中配的 Application Name
14.Hostprocess：建立连接的应用程序在客户端工作站里的进程ID号
15.Cmd：当前正在执行的命令
16.Loginame：登录名
*/

SELECT * FROM Sys.SysProcesses
GO

SELECT SPID = er.session_id
    , ot.Threads
    , RunningThreads = coalesce(rsp.RunningThreads,0)
    , Pct_Comp = er.percent_complete
    , Est_Comp_Time = CASE er.estimated_completion_time WHEN 0 THEN NULL ELSE dateadd(ms, er.estimated_completion_time, getdate()) END 
    , er.status
    , er.command
    , database_name = sd.name
    , BlockedBy = wt.blocking_session_id
    , HeadBlocker = coalesce(hb5.session_id, hb4.session_id, hb3.session_id, hb2.session_id, hb1.session_id)
    , wait_type = coalesce(CASE er.wait_type WHEN 'CXPACKET' THEN 'CXPACKET - ' + sp.lastwaittype1 ELSE sp.lastwaittype1 END, lower(er.last_wait_type)) --Lowercase denotes it's not currently waiting, also noted by a wait time of 0.
    , Wait_Time_Sec = Cast(er.wait_time/1000.0 as DEC(20,3))
    , er.wait_resource
    , Duration_Sec = Cast(DATEDIFF(s, er.start_time, GETDATE()) as DEC(20,0))
    , CPU_Sec = Cast(er.cpu_time/1000.0 as DEC(20,3))
    , Reads_K = Cast(er.reads/1000.0 as DEC(20,3))
    , Writes_K = Cast(er.writes/1000.0 as DEC(20,3))
    , [Statement] = SUBSTRING (st.text, er.statement_start_offset/2,
        abs(CASE WHEN er.statement_end_offset = -1
               THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2 
            ELSE er.statement_end_offset END - er.statement_start_offset)/2)
    , st.text as Query
    , es.login_time
    , es.host_name
    , program_name = CASE LEFT(es.program_name, 29)
                    WHEN 'SQLAgent - TSQL JobStep (Job '
                        THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(es.program_name,32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(es.program_name, 67, len(es.program_name)-67)
                    ELSE es.program_name
                    END  
    , es.client_interface_name
    , es.login_name
    , es.status
    , es.total_scheduled_time
    , es.total_elapsed_time
    , er.start_time
    , es.last_request_start_time
    , es.last_request_end_time
    , er.database_id  
    --, qp.query_plan 
FROM sys.dm_exec_requests er
    INNER JOIN sys.dm_exec_Sessions es on er.session_id=es.session_id
    LEFT JOIN sys.databases sd on er.database_id=sd.database_id
    INNER JOIN (SELECT session_id, count(1) Threads FROM sys.dm_os_tasks GROUP BY session_id) ot on er.session_id=ot.session_id
    LEFT JOIN (SELECT spid, LastWaitType1 = MIN(lastwaittype), LastWaitType2 = MAX(lastwaittype) FROM sysprocesses sp WHERE waittime > 0 AND lastwaittype <> 'cxpacket' GROUP BY spid) sp ON er.session_id = sp.spid
    LEFT JOIN (SELECT spid, RunningThreads = COUNT(1) FROM sysprocesses sp WHERE waittime = 0 GROUP BY spid) rsp ON er.session_id = rsp.spid
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks wt WHERE wt.blocking_session_id <> wt.session_id GROUP BY session_id) wt ON er.session_id = wt.session_id 
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP BY session_id) hb1 ON wt.blocking_session_id = hb1.session_id 
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP BY session_id) hb2 ON hb1.blocking_session_id = hb2.session_id 
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP BY session_id) hb3 ON hb2.blocking_session_id = hb3.session_id 
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP BY session_id) hb4 ON hb3.blocking_session_id = hb4.session_id 
    LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP BY session_id) hb5 ON hb4.blocking_session_id = hb5.session_id 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st  
    --CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE er.session_id <> @@SPID
    --AND es.host_name like '%%'
    --AND er.session_id = 2702
ORDER BY er.percent_complete DESC, er.cpu_time DESC, er.session_id

--Use the below command to get the last input of an open session id
--dbcc inputbuffer(61)

SELECT COUNT(*)

FROM sys.dm_tran_active_transactions T

JOIN sys.dm_tran_session_transactions S

ON S.transaction_id = T.transaction_id

WHERE transaction_begin_time < DATEADD(MS, -30000, GETDATE())

--2. With identifying database

SELECT d.name, COUNT(*) as Tx, MIN(transaction_begin_time) as Earliest

FROM sys.dm_tran_active_transactions tat
INNER JOIN sys.dm_exec_requests er
ON tat.transaction_id = er.transaction_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)
JOIN sys.databases d

ON d.database_id = er.database_id

WHERE transaction_begin_time <=  DATEADD(MS, -30000, GETDATE())    

GROUP BY d.name
GO

select * from sys.dm_exec_sessions
GO
--Mapping System Tables to System Views (Transact-SQL)
---https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mapping-system-tables-to-system-views-transact-sql


--查询得到数据库的名字
Select name,dbid from master.sys.sysdatabases
GO


--表中的每条记录都对应着数据库中的数据文件或日志文件的一次备份信息。表中的字段说明了备份时文件的属性。通俗的说，是备份文件的详细表。

SELECT * FROM [msdb].[dbo].backupfile
GO
---[compressed_backup_size],
SELECT TOP 1000 [backup_set_id],a.[media_set_id],[expiration_date],[name],[user_name],[software_major_version],[backup_start_date],[backup_finish_date],[type],[compatibility_level], [backup_size],[database_name] ,[server_name], [is_password_protected],[recovery_model],[is_damaged] ,[begins_log_chain], b.physical_device_name    

FROM [msdb].[dbo].[backupset] a,[msdb].[dbo].[backupmediafamily] b  

where a.media_set_id=b.media_set_id  order by backup_set_id desc
GO

--备份时，数据库中的每个文件组占一行。
SELECT * FROM [msdb].[dbo].backupfilegroup

--每个媒体簇在表中占一行。如果媒体簇驻留在镜像媒体集中，则对于媒体集中的每个镜像服务器，该媒体簇都具有一个单独的行。该表存储在 msdb 数据库中。媒体簇，即备份后的物理文件。
backupmediafamily


--每个备份媒体集在表中占一行
 SELECT * FROM [msdb].[dbo].backupmediaset
 
 --每个备份媒体集在表中占一行
 
  SELECT * FROM [msdb].[dbo].backupset
--
select top 150 a.run_date,a.run_time, b.name,step_id,step_name,a.message,a.run_status,a.run_duration
                                from msdb.dbo.sysjobhistory a ,msdb.dbo.sysjobs b
                                where a.job_id=b.job_id and name not in('job_exclude') and a.step_id>0
                                order by run_date DESC
 GO
 
 --记录当前 SQL Server 代理作业活动和状态。    
 SELECT * FROM [msdb].[dbo].sysjobactivity
 
 --sysjobhistory 表 包含有关 SQL Server 代理执行预定作业的信息 通俗易懂的说，就是记录 job 执行的历史情况，该表比较有用，能查看job执行的时间、状态、完成信息等。
  SELECT * FROM [msdb].[dbo].sysjobhistory
  
  --SQL Server 代理执行的各个预定作业的信息  sysjobs 表
  
  SELECT * FROM [msdb].[dbo].sysjobs
  
  
  --sysjobservers 表 MSDN：存储特定作业与一个或多个目标服务器的关联或关系。
  
  SELECT * FROM [msdb].[dbo].sysjobservers
  
  
  --sysjobschedules 表 job(作业)下次执行的时间信息 --塗聚文
   SELECT * FROM [msdb].[dbo].sysjobschedules
  --sysjobsteps  表 包含 SQL Server 代理要执行的作业中的各个步骤的信息。
   SELECT * FROM [msdb].[dbo].sysjobsteps
  
  --sysjobstepslogs 表 包含所有 SQL Server 代理作业步骤的作业步骤日志，这些作业步骤配置为将作业步骤输出写入表中
  
  SELECT * FROM [msdb].[dbo].sysjobstepslogs
 
    
---Sql Server 查看所有存储过程或视图的位置及内容  涂聚文  Geovin Du     
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
GO
SELECT * FROM Sys.Sql_Modules
GO