sql server:查詢系統表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411

---查看所有存储过程或视图的位置 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