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 -- sysnamesys.sp_helptext dbo.CalcCustomerPrice
GOsys.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 
GOEXEC SYS.sp_databases
GO--返回可在當前環境中查詢的對象列表。也就是說，返回任何能夠在 FROM 子句中出現的對象（不包括同義詞對象）EXEC sp_tables @table_owner='dbo'
GOEXEC sp_tables   @table_name = '%',  @table_owner = 'Person',  @table_qualifier = 'AdventureWorks2012';  
GO
--返回針對指定的表或索引視圖的所有索引和統計信息的列表，用于觀察表的索引情況，很有用
EXEC sp_statistics 'APClericalCostDetail'
GO--返回 SQL Server、數據庫網關或基礎數據源的屬性名稱和匹配值的列表。
EXEC sys.sp_server_info
GOEXEC sp_server_info
GOEXEC 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 -- intEXEC 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 -- intGOEXEC 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
GOEXEC 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
GOEXEC 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
GOEXEC  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
GOEXEC 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
GOSELECT 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 = -1THEN 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_nameEND  , 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 erINNER JOIN sys.dm_exec_Sessions es on er.session_id=es.session_idLEFT JOIN sys.databases sd on er.database_id=sd.database_idINNER JOIN (SELECT session_id, count(1) Threads FROM sys.dm_os_tasks GROUP BY session_id) ot on er.session_id=ot.session_idLEFT 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.spidLEFT JOIN (SELECT spid, RunningThreads = COUNT(1) FROM sysprocesses sp WHERE waittime = 0 GROUP BY spid) rsp ON er.session_id = rsp.spidLEFT 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 TJOIN sys.dm_tran_session_transactions SON S.transaction_id = T.transaction_idWHERE transaction_begin_time < DATEADD(MS, -30000, GETDATE())--2. With identifying databaseSELECT d.name, COUNT(*) as Tx, MIN(transaction_begin_time) as EarliestFROM 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 dON d.database_id = er.database_idWHERE transaction_begin_time <=  DATEADD(MS, -30000, GETDATE())    GROUP BY d.name
GOselect * 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_durationfrom msdb.dbo.sysjobhistory a ,msdb.dbo.sysjobs bwhere a.job_id=b.job_id and name not in('job_exclude') and a.step_id>0order by run_date DESCGO--記錄當前 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