SELECT o.name AS tableName,c.name AS colName,k.keyno AS sort
FROM sysindexes i
JOIN sysindexkeys k ON i.id = k.id AND i.indid = k.indid
JOIN sysobjects o ON i.id = o.id
JOIN syscolumns c ON i.id=c.id AND k.colid = c.colid
WHERE 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 execTime
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50
AND session_Id NOT IN (@@SPID)
--and sp.dbid=7
ORDER BY DATEDIFF ( millisecond,start_time, GETDATE() ) desc
-- 获取主键排序
SELECT o.name AS tableName,c.name AS colName,k.keyno AS sort
FROM sysindexes i
JOIN sysindexkeys k ON i.id = k.id AND i.indid = k.indid
JOIN sysobjects o ON i.id = o.id
JOIN syscolumns c ON i.id=c.id AND k.colid = c.colid
WHERE 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