SELECT o.name AS tableName,c.name AS colName,k.keyno AS sortFROM sysindexes iJOIN sysindexkeys k ON i.id = k.id AND i.indid = k.indidJOIN sysobjects o ON i.id = o.idJOIN syscolumns c ON i.id=c.id AND k.colid = c.colidWHERE EXISTS( SELECT 1 FROM sysobjects WHERE name = i.name) AND o.name=@tableName--表名称ORDER BY o.name,k.colid
查找所有存储过程包含了哪些表，函数
select * from sys.all_sql_modules where definition like '%[NAME]%'
查找作业步骤里执行了哪些存储过程
select * from [msdb].[dbo].[sysjobsteps] where command like '%recmsg_orderresult%'
查找所有作业信息
select * from [msdb].[dbo].[sysjobs] where job_id = ''
--查看数据库后台进程
SELECT session_Id, --ecid, --sp.dbid, --DB_NAME(sp.dbid) as DB_NM, --nt_username, er.status, wait_type, SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) as CurrentSQL, qt.text, --program_name, --Hostname, start_time, DATEDIFF ( millisecond,start_time, GETDATE() ) AS execTimeFROM sys.dm_exec_requests erINNER JOIN sys.sysprocesses sp ON er.session_id = sp.spidCROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qtWHERE session_Id > 50 AND session_Id NOT IN (@@SPID) --and sp.dbid=7ORDER BY DATEDIFF ( millisecond,start_time, GETDATE() ) desc
-- 获取主键排序
SELECT o.name AS tableName,c.name AS colName,k.keyno AS sortFROM sysindexes iJOIN sysindexkeys k ON i.id = k.id AND i.indid = k.indidJOIN sysobjects o ON i.id = o.idJOIN syscolumns c ON i.id=c.id AND k.colid = c.colidWHERE EXISTS( SELECT 1 FROM sysobjects WHERE name = i.name) AND o.name=@tableName--表名称ORDER BY o.name,k.colid
-- 获取数据库中所有表名及数据条数
select b.name as tablename , c.row_count as datacount from sys.indexes a , sys.objects b , sys.dm_db_partition_stats c where a.[object_id] = b.[object_id] AND b.[object_id] = c.[object_id] AND a.index_id = c.index_id AND a.index_id < 2 AND b.is_ms_shipped = 0 order by b.name