select object_name(object_id),* from sys.filetables 
57. 查询filetable表testdb.dbo.table1中的文件完整路径名称
SELECT FileTableRootPath()+[file_stream].GetFileNamespacePath(),name FROM testdb.dbo.table158. 查询所有job的状态是否running SELECT sj.Name,CASEWHEN sja.start_execution_date IS NULL THEN 'Not running'WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'END AS 'RunStatus' FROM msdb.dbo.sysjobs sj JOIN msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id WHERE session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) order by RunStatus desc;59. 锁表的四种用法
TABLOCKX SELECT * FROM table WITH (TABLOCKX)查询过程中，其他会话无法查询、更新此表，直到查询过程结束TABLOCK SELECT * FROM table WITH (TABLOCK)查询过程中，其他会话可以查询，但是无法更新此表，直到查询过程结束 HOLDLOCK SELECT * FROM table WITH (HOLDLOCK)查询过程中，其他会话可以查询，但是无法更新此表，直到查询过程结束NOLOCK SELECT * FROM table WITH (NOLOCK)查询过程中，其他会话可以查询、更新此表 
60. 查询某个发布XX，发布的数据库对象的2种方法
发布数据库上执行(数据来源这三张表distribution.dbo.MSpublications、distribution.dbo.MSarticles、sysarticlecolumns) select a.article,a.source_object,a.destination_object,b.colid from (select article,article_id,source_object,destination_object from [distribution].[dbo].MSarticles where publication_id in ( select publication_id from [distribution].[dbo].MSpublications where publication='XX' ) ) a inner join (select * from replicate1.dbo.sysarticlecolumns) b on a.article_id=b.artid order by a.article订阅数据库上执行 select distinct articlefrom MSreplication_objects where publication='XX'61. 查询发布信息，发布名称，发布名称对应的发布序号
Select * from distribution.dbo.MSpublications 
62. 查询发布名里面的发布对象的信息，包含表、视图、存储过程等
Select * fromdistribution.dbo.MSarticles 
63. 监控发布订阅是否有异常，执行以下5条语句即可
select * from [distribution].[dbo].[MSlogreader_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].[MSdistribution_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].[MSsnapshot_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].MSrepl_errors order by 2 descselect * from msdb.dbo.sysreplicationalerts order by 7 desc 
64. 查询XX表的索引信息
SELECT a.name index_name,c.name table_name,d.name column_name FROM sysindexes a JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid JOIN sysobjects c ON b.id=c.id JOIN syscolumns d ON b.id=d.id= AND b.colid=d.colid WHERE a.indid NOT IN(0,255) AND c.name in ('XX') 
65. 生成sql语句的执行计划(select XXX为例，当然select XXX也可以换成执行存储过程比如exec pro_XXX，都是只生成执行计划，不产生结果集，不会执行存储过程)
SET SHOWPLAN_ALL ON; GO select XXX GO SET SHOWPLAN_ALL OFF; GO 或 SET SHOWPLAN_XML ON; GO select XXX GO SET SHOWPLAN_XML OFF; GO 
66. 查询名称为XXX的job的最后一次运行成功的时间
SELECT TOP 1 CONVERT(DATETIME, RTRIM(run_date))+ ((run_time / 10000 * 3600) + ((run_time % 10000) / 100 * 60) + (run_time % 10000) % 100) / (86399.9964) FROM msdb.dbo.sysjobhistory jobhis inner join msdb.dbo.sysjobsjobs on jobhis.job_id = jobs.job_id AND jobhis.step_id = 0 AND jobhis.run_status = 1 and jobs.name='XXX' ORDER BY 1 DESC67. 查询某张分区表的总行数和大小，比如表为crm.EmailLog
exec sp_spaceused 'crm.EmailLog'; 
68. 查询某张分区表的信息，每个分区有多少行，比如表为crm.EmailLog
select convert(varchar(50), ps.name ) as partition_scheme, p.partition_number, convert(varchar(10), ds2.name ) as filegroup, convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, str(p.rows, 9) as rows from sys.indexes i join sys.partition_schemes ps on i.data_space_id = ps.data_space_id join sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id join sys.partitions p on dds.destination_id = p.partition_number and p.object_id = i.object_id and p.index_id = i.index_id join sys.partition_functions pf on ps.function_id = pf.function_id LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id and v.boundary_id = p.partition_number - pf.boundary_value_on_right WHERE i.object_id = object_id('crm.EmailLog') and i.index_id in (0, 1) order by p.partition_number