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