SQLServer 维护脚本分享（09）相关文件读取

/********************【读取跟踪文件（trc）】********************/--查看事件类型描述SELECT tc.name,te.trace_event_id,te.name,tc.type,CASE tc.type WHEN 0 THEN '普通' WHEN 1 THEN '连接' WHEN 2 THEN '错误' END AS [type]FROM sys.trace_categories tc INNER JOIN sys.trace_events te on te.category_id = tc.category_idORDER BY tc.name,trace_event_id--查看跟踪记录DECLARE @path NVARCHAR(1000)SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +'\log.trc'FROM sys.traces WHERE  id = 1SELECT * FROM ::fn_trace_gettable(@path, 0)WHERE EventClass in(46,47,164)--返回有关的跟踪文件信息SELECT * FROM sys.tracesSELECT * FROM ::fn_trace_getinfo(0) /************************【读取txt文件】************************//*FILE: D:\kk.txtID Name1 KK2 MM3 HH*/--DROP TABLE #tempCREATE TABLE #temp(ID VARCHAR(10),Name VARCHAR(10))  BULK INSERT #temp FROM 'D:\kk.txt'  WITH(      FIELDTERMINATOR=' ',      ROWTERMINATOR='\n',FIRSTROW = 2)  SELECT * FROM #temp/************************【读取excel文件】************************/exec sp_configure 'show advanced options',1 --启用高级配置选项设置  reconfigure;  exec sp_configure 'Ad Hoc Distributed Queries',1 --启用 OPENROWSET,OpenDataSource  reconfigure;  /*  服务器对象-->链接服务器-->访问接口-->Microsoft.Jet.OLEDB.4.0（右键属性）-->把[允许进程内]前面的钩去掉。  HDR: 表示第一行是否字段名,默认是YES  IMEX: 0/1/2/,分别为 导出/导入/混合模式,  */  --服务器对象-->链接服务器-->访问接口：Microsoft.Jet.OLEDB.4.0 / Microsoft.ACE.OLEDB.12.0SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DATABASE=D:\KK.xls',sheet1$)  SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 5.0;HDR=YES;IMEX=1;DATABASE=D:\KK.xls',sheet1$)  SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[sheet1$]      SELECT * FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 12.0')...[sheet1$]   /************************【读取xml文件】************************/--参考：https://technet.microsoft.com/zh-cn/ms175160.aspx/*FILE: D:\kk.xml<?xml version="1.0" ?><book><bookA><ID>1</ID><Title>KK</Title><Price>99</Price></bookA></book>*/DECLARE @docHandle int  DECLARE @xml xml SELECT @xml=bulkcolumn FROM OPENROWSET( BULK 'D:\kk.xml', SINGLE_BLOB) AS x  EXEC sp_xml_preparedocument @docHandle OUTPUT, @xml  --结果插入临时表 #bookA 。字段的大小写须与xml中对应一致！SELECT * into #bookA FROM OPENXML (@docHandle, '/book/bookA',2)  WITH (ID INT,Title VARCHAR(100),Name DECIMAL(10,2)) EXEC sp_xml_removedocument @docHandle; GOSELECT * FROM #bookA/************************【导入导出 word 文件】************************/--导入文档数据--DROP TABLE DocumentsCREATE TABLE Documents(ID INT,Document VARBINARY(MAX))GODECLARE @Doc AS VARBINARY(MAX)SELECT @doc = CAST(bulkcolumn AS VARBINARY(MAX))FROM OPENROWSET( BULK 'C:\Users\huangzc\Desktop\脚本.docx', SINGLE_BLOB ) AS x INSERT INTO Documents (ID, Document)VALUES (1, @doc )GO--导出文档数据exec sp_configure 'xp_cmdshell',1reconfigure with overridegoDECLARE @Command NVARCHAR(4000) SET @Command = 'bcp "SELECT Document FROM Demo.dbo.Documents" queryout "C:\Users\huangzc\Desktop\NEW.docx" -T -n -CSHAH\SQL2008' EXEC xp_cmdshell @Command goexec sp_configure 'xp_cmdshell',0reconfigure with overridego/*若 bcp 错误:Unable to load BCP resource DLL.  BCP cannot continue.解决方法:更名或删除以下文件C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Resources\2052\bcp.rll*//************************【读取扩展会话事件文件】************************/SELECT * FROM sys.server_event_session_fields  SELECT * FROM sys.fn_MSxe_read_event_stream (N'system_health.xel', 1)  SELECT * FROM sys.fn_xe_file_target_read_file(N'system_health.xel', NULL, NULL, NULL)   /************************【读取事务日志文件】************************/--查看当前数据库事务日志信息：DBCC LOG('AdventureWorks2014',0)/*dbcc log(dbname,4) --(n=0,1,2,3,4)1 - 更多信息plus flags, tags, row length2 - 非常详细的信息plus object name, index name,page id, slot id3 - 每种操作的全部信息4 - 每种操作的全部信息加上该事务的16进制信息*/--查看当前数据库事务日志信息：SELECT * FROM sys.fn_dblog(NULL, NULL)WHERE Operation in('LOP_MODIFY_ROW','LOP_INSERT_ROWS','LOP_DELETE_ROWS')ORDER BY [Current LSN] DESC--查看备份中的事务日志信息：SELECT * FROM sys.fn_dump_dblog(NULL, NULL, N'DISK', 1,N'D:\MyDatabase_backup_log_2015_05_30_000000_001.trn', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)ORDER BY [Current LSN] DESC/**********************【读取SQL Sever日志或代理日志】**********************/--SQL Sever日志信息exec xp_enumerrorlogs--搜索SQL Sever日志 或 代理日志exec xp_readerrorlogexec xp_readerrorlog 0,1,'DeadLock',NULL,'2015-01-01','2015-01-10','DESC'/*1. 存档编号2. 日志类型(1为 SQL Server 日志,2为SQL Agent日志)3. 查询包含的字符串4. 查询包含的字符串5. LogDate开始时间6. 结果排序,按LogDate排序(可以为降序"Desc" Or 升序"Asc")7. 结果排序,按LogDate排序(可以为降序"Desc" Or 升序"Asc") *//************************【其他信息】************************/--逻辑磁盘可用空间exec master.dbo.xp_fixeddrives--所有数据库事务日志大小及百分比 dbcc sqlperf(logspace)--缓存计划select * from sys.syscacheobjectsselect * from sys.dm_exec_cached_plans--性能计数器select * from sys.dm_os_performance_countersselect * from master.sys.sysperfinfo--获取批处理代码SELECT * FROM sys.dm_exec_query_statsSELECT * FROM sys.fn_get_sql(@plan_handle)SELECT * FROM sys.dm_exec_sql_text(@plan_handle)DECLARE @Handle binary(20)DECLARE @SPID INTSELECT @SPID = spid,@Handle = sql_handle FROM sysprocesses WHERE spid = 101SELECT * FROM ::fn_get_sql(@Handle) DBCC INPUTBUFFER(@SPID)/*******************************************************************************/