5.常用脚本
1.当前正在运行进程会话的请求信息
use DB_NAME
set nocount on
set transaction isolation level read uncommitted
;with cte as
(
select t.session_id as spid,t.status,t.blocking_session_id as blocked
,t.program_name
,str(1.0* t.total_elapsed_time/1000,16,0) as duration_s
,str(1.0*(t.cpu_time+t.wait_time)/1000,16,0) as inter_duration_s
,str(1.0*(t.cpu_time)/1000,16,0) as cpu_time_s
,str(1.0*(t.wait_time)/1000,16,0) as wait_time_s
,t.granted_query_memory*8/1024 as mb,t.logical_reads,t.reads,t.writes
,t.start_time,t.login_time
,t.command,t.wait_type,t.wait_resource,s.text
from
(
select b.session_id,b.status,a.blocking_session_id,b.program_name
,a.command,a.sql_handle
,a.cpu_time,a.wait_time,a.total_elapsed_time,a.granted_query_memory
,a.start_time,b.login_time
,a.logical_reads,a.reads,a.writes
,a.wait_type,a.wait_resource
from sys.dm_exec_requests a inner join sys.dm_exec_sessions b on b.session_id=a.session_id
where b.program_name is not null
and b.session_id <> @@spid
) t cross apply sys.dm_exec_sql_text(t.sql_handle) s
)
select
(case when exists(select * from cte b where b.spid=a.spid and b.blocked=0 and (a.spid in (select blocked from cte c))) then 'yes' else '' end) as is_blocker
,*
from cte a
where
(blocked>0) or
(spid in (select blocked from cte)) or
duration_s>5
order by blocked,program_name,spid
- 查看当前正在执行的sql
SELECT [Spid] = session_id , ecid ,
[Database] = DB_NAME(sp.dbid) ,[User] = nt_username ,
[Status] = er.status , [Wait] = wait_type ,
[Individual Query] = 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) ,
[Parent Query] = qt.text , Program = program_name ,hostname , nt_domain , start_time
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 -- Ignore system spids.
AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.
ORDER BY 1 ,
2
2.查看索引碎片
use DB_NAME
set nocount on
print db_name()
declare @tab_name sysname
set @tab_name='wfpuser_a0113'
--内部碎片
select '【内部】碎片' as frag_type
,ob.name as object_name
,ix.index_id,ix.name as index_name
,ps.index_level
,ps.partition_number
,ps.record_count
,ps.page_count
,ps.fragment_count
,str(ps.avg_fragmentation_in_percent,16,2) as [avg_frag(%)]
,str(ps.avg_fragment_size_in_pages,16,1) as [avg_frag_pages]
,ps.forwarded_record_count
,ps.avg_record_size_in_bytes
,str(ps.avg_page_space_used_in_percent,16,2) as [avg_page_space_used(%)]
from sys.dm_db_index_physical_stats(db_id(),object_id(@tab_name),default, default, 'detailed') ps
inner join sys.indexes ix on ix.object_id = ps.object_id and ix.index_id = ps.index_id
inner join sys.objects ob on ob.object_id=ix.object_id
where ob.is_ms_shipped=0
union all
--外部碎片
select '外部碎片' as frag_type
,ob.name as object_name
,ix.index_id,ix.name as index_name
,ps.index_level
,ps.partition_number
,ps.record_count
,ps.page_count
,ps.fragment_count
,str(ps.avg_fragmentation_in_percent,16,2) as [avg_frag(%)]
,str(ps.avg_fragment_size_in_pages,16,1) as [avg_frag_pages]
,ps.forwarded_record_count
,ps.avg_record_size_in_bytes
,str(ps.avg_page_space_used_in_percent,16,2) as [avg_page_space_used(%)]
from sys.dm_db_index_physical_stats(db_id(),object_id(@tab_name),default, default, 'limited') ps
inner join sys.indexes ix on ix.object_id = ps.object_id and ix.index_id = ps.index_id
inner join sys.objects ob on ob.object_id=ix.object_id
where ob.is_ms_shipped=0
order by object_name,index_id,frag_type
外部索引碎片对性能影响不大
生成索引语句
use DB_NAME
SELECT OBJECT_SCHEMA_NAME(B.OBJECT_ID) 架构,OBJECT_NAME(B.OBJECT_ID) 表名,B.NAME 索引名,ROUND(A.AVG_FRAGMENTATION_IN_PERCENT,2) 碎片率,
CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN N'重新生成索引' ELSE N'重新组织索引' END 处理方式,
'ALTER INDEX '+QUOTENAME(B.NAME)+' ON '+QUOTENAME(OBJECT_SCHEMA_NAME(B.OBJECT_ID))+'.'+QUOTENAME(OBJECT_NAME(B.OBJECT_ID))+' '
+CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN 'REBUILD' ELSE 'REORGANIZE' END 生成SQL语句
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A INNER JOIN sys.indexes B ON A.OBJECT_ID=B.OBJECT_ID AND A.INDEX_ID=B.INDEX_ID
WHERE A.AVG_FRAGMENTATION_IN_PERCENT>5 AND B.INDEX_ID>0
AND OBJECT_NAME(B.OBJECT_ID) IN ('Order') --指定表
ORDER BY CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN N'重新生成索引' ELSE N'重新组织索引' END,OBJECT_NAME(B.OBJECT_ID),B.INDEX_ID
索引缺失
- 建议
use DB_NAME
SELECT TOP 100
statement AS 表 ,
equality_columns AS 相等列 ,
inequality_columns AS 不相等列 ,
included_columns AS 包含列 ,
user_scans + user_seeks AS 总查询次数 ,
avg_user_impact AS 平均百分比收益 ,
avg_total_user_cost AS 平均成本 ,
avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS 可能改进 ,
'CREATE INDEX [index_' + obj.name + '_'
+ CONVERT(VARCHAR(32), GS.group_handle) + '_'
+ CONVERT(VARCHAR(32), D.index_handle) + ']' + ' ON ' + [statement]
+ ' (' + ISNULL(equality_columns, '')
+ CASE WHEN equality_columns IS NOT NULL
AND inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
+ included_columns
+ ')', '') AS Create_Index_Syntax
FROM sys.dm_db_missing_index_details AS D
INNER JOIN sys.dm_db_missing_index_groups G ON G.index_handle = D.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
INNER JOIN sys.objects AS obj ON obj.object_id = OBJECT_ID([statement])
AND obj.type = 'U'
3.无效的索引、高成本索引
use DB_NAME
set nocount on
go
--无效索引排序
select top 20
db_name() as db_name
,schema_name(o.schema_id) as schema_name
,o.name as object_name
,i.name as index_name,i.is_unique_constraint,(case i.index_id when 1 then 'is_clustered' else '' end) as is_clustered
,ir.rowcnt
,s.user_updates
,(s.user_lookups + s.user_scans + s.user_seeks) as [retrieval usage] --查询使用次数
,s.system_seeks + s.system_scans + s.system_lookups as [system usage]
,'DROP INDEX ' + quotename(o.name) + '.' + quotename(i.name) as [ -- dsql]
from sys.dm_db_index_usage_stats s
inner join sys.indexes i on s.database_id=db_id() and s.[object_id] = i.[object_id] and s.index_id = i.index_id
inner join sys.objects o on i.object_id = o.object_id
inner join sysindexes ir on ir.id=i.object_id and ir.indid=i.index_id
where s.database_id = db_id()
and o.is_ms_shipped = 0
and i.name is not null
--and s.user_seeks = 0
--and s.user_scans = 0
--and s.user_lookups = 0
and (s.user_lookups + s.user_scans + s.user_seeks)=0
order by s.user_updates desc
go
-- 维护成本 排序
select top 20
db_name() as db_name
,schema_name(o.schema_id) as schema_name
,o.name as object_name
,i.name as index_name,i.is_unique_constraint,(case i.index_id when 1 then 'is_clustered' else '' end) as is_clustered
,ir.rowcnt
,s.user_updates -- as [update usage] --更新成本
,(s.user_seeks + s.user_scans + s.user_lookups) as [retrieval usage] --查询使用次数
,(s.user_updates) - (s.user_seeks + user_scans + s.user_lookups) as [maintenance cost] --用户维护成本
,s.system_seeks + s.system_scans + s.system_lookups as [system usage] --系统内部维护次数，--内部维护成本
,s.last_user_seek
,s.last_user_scan
,s.last_user_lookup
,'DROP INDEX ' + quotename(o.name) + '.' + quotename(i.name) as [ -- dsql]
from sys.dm_db_index_usage_stats s
inner join sys.indexes i on s.database_id=db_id() and s.[object_id] = i.[object_id] and s.index_id = i.index_id
inner join sys.objects o on i.object_id = o.object_id
inner join sysindexes ir on ir.id=i.object_id and ir.indid=i.index_id
where s.database_id = db_id()
and o.is_ms_shipped = 0
and i.name is not null
and (s.user_seeks + s.user_scans + s.user_lookups) > 0
order by [maintenance cost] desc
4.查看索引所占空间
use hantest
SELECT
db_name() as DbName,
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,
SUM(a.used_pages) * 8 AS 总使用空间KB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总使用空间MB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS 碎片化空间KB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS 碎片化空间MB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.is_ms_shipped = 0
AND i.OBJECT_ID > 0
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
总共占用空间MB desc
重构数据库中所有表
USE My_Database;
DECLARE @name varchar(100)
DECLARE authors_cursor CURSOR FOR Select [name] from sysobjects where xtype='u' order by id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX (@name, '', 90)
FETCH NEXT FROM authors_cursor INTO @name
END
deallocate authors_cursor
查询未使用过的索引
use DB_NAME
SELECT DB_NAME(diu.database_id) AS DatabaseName ,
s.name +'.' +QUOTENAME(o.name) AS TableName ,
i.index_id AS IndexID ,
i.name AS IndexName ,
CASE WHEN i.is_unique =1 THEN 'UNIQUE INDEX'
ELSE 'NOT UNIQUE INDEX' END AS IS_UNIQUE,
CASE WHEN i.is_disabled=1 THEN 'DISABLE'
ELSE 'ENABLE' END AS IndexStatus,
o.create_date AS IndexCreated,
STATS_DATE(o.object_id,i.index_id) AS StatisticsUpdateDate,
diu.user_seeks AS UserSeek ,
diu.user_scans AS UserScans ,
diu.user_lookups AS UserLookups ,
diu.user_updates AS UserUpdates ,
p.TableRows ,
'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(diu.object_id)) +';' AS 'Drop Index Statement'
FROM sys.dm_db_index_usage_stats diu
INNER JOIN sys.indexes i ON i.index_id = diu.index_id
AND diu.object_id = i.object_id
INNER JOIN sys.objects o ON diu.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN ( SELECT SUM(p.rows) TableRows ,
p.index_id ,
p.object_id
FROM sys.partitions p
GROUP BY p.index_id ,
p.object_id
) p ON p.index_id = diu.index_id
AND diu.object_id = p.object_id
WHERE OBJECTPROPERTY(diu.object_id, 'IsUserTable') = 1
AND diu.database_id = DB_ID()
AND i.is_primary_key = 0 --排除主键索引
AND i.is_unique_constraint = 0 --排除唯一索引
AND diu.user_updates <> 0 --排除没有数据变化的索引
AND diu.user_lookups = 0
AND diu.user_seeks = 0
AND diu.user_scans = 0
AND i.name IS NOT NULL --排除那些没有任何索引的堆表
ORDER BY ( diu.user_seeks + diu.user_scans + diu.user_lookups ) ASC,diu.user_updates DESC;
查询表下索引使用情况
select db_name(database_id) as N'数据库名称',
object_name(a.object_id) as N'表名',
b.name N'索引名称',
user_seeks N'用户索引查找次数',
user_scans N'用户索引扫描次数',
max(last_user_seek) N'最后查找时间',
max(last_user_scan) N'最后扫描时间',
max(rows) as N'表中的行数'
from sys.dm_db_index_usage_stats a join
sys.indexes b
on a.index_id = b.index_id
and a.object_id = b.object_id
join sysindexes c
on c.id = b.object_id
where database_id=db_id('数据库名称') --指定数据库
and object_name(a.object_id) not like 'sys%'
and object_name(a.object_id) like '表名' --指定索引表
and b.name is not null
--and b.name like '索引名' --指定索引名称 可以先使用 sp_help '你的表名' 查看表的结构和所有的索引信息
group by db_name(database_id) ,
object_name(a.object_id),
b.name,
user_seeks ,
user_scans
order by user_seeks,user_scans,object_name(a.object_id)
针对所有库清理碎片
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
BEGIN
-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
END
ELSE
BEGIN
-- SQL 2000 command
DBCC DBREINDEX(@Table,' ',@fillfactor)
END
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
5.查看死锁
DECLARE @SessionName SysName
SELECT @SessionName = 'system_health'
IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN
DROP TABLE #Events
END
DECLARE @Target_File NVarChar(1000)
, @Target_Dir NVarChar(1000)
, @Target_File_WildCard NVarChar(1000)
SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = @SessionName
AND t.target_name = 'event_file'
SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File)))
SELECT @Target_File_WildCard = @Target_Dir + '\' + @SessionName + '_*.xel'
--Keep this as a separate table because it's called twice in the next query. You don't want this running twice.
SELECT DeadlockGraph = CAST(event_data AS XML)
, DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset)
INTO #Events
FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F
WHERE event_data like '<event name="xml_deadlock_report%'
;WITH Victims AS
(
SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)')
, e.DeadlockID
FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims)
)
, DeadlockObjects AS
(
SELECT DISTINCT e.DeadlockID
, ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)')
FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources)
)
SELECT *
FROM
(
SELECT e.DeadlockID
, TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime')
, DeadlockGraph
, DeadlockObjects = substring((SELECT (', ' + o.ObjectName)
FROM DeadlockObjects o
WHERE o.DeadlockID = e.DeadlockID
ORDER BY o.ObjectName
FOR XML PATH ('')
), 3, 4000)
, Victim = CASE WHEN v.VictimID IS NOT NULL
THEN 1
ELSE 0
END
, SPID = Deadlock.Process.value('@spid', 'int')
, ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)')
, LockMode = Deadlock.Process.value('@lockMode', 'char(1)')
, Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)')
, ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29)
WHEN 'SQLAgent - TSQL JobStep (Job '
THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67)
ELSE Deadlock.Process.value('@clientapp', 'varchar(100)')
END
, HostName = Deadlock.Process.value('@hostname', 'varchar(20)')
, LoginName = Deadlock.Process.value('@loginname', 'varchar(20)')
, InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process)
LEFT JOIN Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)')
) X
ORDER BY DeadlockID DESC
- 另一个方式
DECLARE @tab TABLE(NAME varchar(100),value varchar(200));
INSERT INTO @tab EXEC('DBCC OPENTRAN WITH TABLERESULTS');
SELECT name,CAST(value AS DATETIME) startDate,getdate() currentDate
,DATEDIFF(s,CAST(value AS DATETIME),getdate()) diffsecond FROM @tab WHERE name in
('OLDACT_STARTTIME')
SELECT spid,
blocked,
DB_NAME(sp.dbid) AS DBName,
program_name,
waitresource,
lastwaittype,
sp.loginame,
sp.hostname,
a.[Text] AS [TextData],
SUBSTRING(A.text, sp.stmt_start / 2,
(CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end
END - sp.stmt_start) / 2) AS [current_cmd]
FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
WHERE spid =(SELECT CASE WHEN ISNUMERIC(value)=0 THEN -1 ELSE value end FROM @tab WHERE name in
('OLDACT_SPID') )
- 创建死锁存储过程
use master
go
CREATE procedure sp_who_lock
as
begin
declare @spid int
declare @blk int
declare @count int
declare @index int
declare @lock tinyint
set @lock=0
create table #temp_who_lock
(
id int identity(1,1),
spid int,
blk int
)
if @@error<>0 return @@error
insert into #temp_who_lock(spid,blk)
select 0 ,blocked
from (select * from master..sysprocesses where blocked>0)a
where not exists(select * from master..sysprocesses where a.blocked =spid and blocked>0)
union
select spid,blocked from master..sysprocesses where blocked>0
if @@error<>0 return @@error
select @count=count(*),@index=1 from #temp_who_lock
if @@error<>0 return @@error
if @count=0
begin
select '没有阻塞和死锁信息'
return 0
end
while @index<=@count
begin
if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock
where id<=@index and a.blk=spid))
begin
set @lock=1
select @spid=spid,@blk=blk from #temp_who_lock where id=@index
select '引起数据库死锁的是: '+ CAST(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
select @spid, @blk
dbcc inputbuffer(@spid)
dbcc inputbuffer(@blk)
end
set @index=@index+1
end
if @lock=0
begin
set @index=1
while @index<=@count
begin
select @spid=spid,@blk=blk from #temp_who_lock where id=@index
if @spid=0
select '引起阻塞的是:'+cast(@blk as varchar(10))+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10))
+'阻塞,其当前进程执行的SQL语法如下'
dbcc inputbuffer(@spid)
dbcc inputbuffer(@blk)
set @index=@index+1
end
end
drop table #temp_who_lock
return 0
end
GO
6.查询逻辑读取最高的sql
超过10w，则需要是否优化了
SELECT TOP ( 25 )
P.name AS [SP Name] ,
Deps.total_logical_reads AS [TotalLogicalReads] ,
deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads] ,
deps.execution_count ,
ISNULL(deps.execution_count / DATEDIFF(SECOND, deps.cached_time,
GETDATE()), 0) AS [Calls/Second] ,
deps.total_elapsed_time ,
deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time] ,
deps.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS deps ON p.[Object_id] = deps.[Object_id]
WHERE deps.Database_id = DB_ID()
ORDER BY deps.total_logical_reads DESC
7.查询表结构
use db_name
SELECT 表名 = CASE WHEN a.colorder = 1 THEN d.name
ELSE ''
END ,
表说明 = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '')
ELSE ''
END ,
字段序号 = a.colorder ,
字段名 = a.name ,
标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1
THEN '√'
ELSE ''
END ,
主键 = CASE WHEN EXISTS ( SELECT 1
FROM sysobjects
WHERE xtype = 'PK'
AND parent_obj = a.id
AND name IN (
SELECT name
FROM sysindexes
WHERE indid IN (
SELECT
indid
FROM sysindexkeys
WHERE id = a.id
AND colid = a.colid ) ) )
THEN '√'
ELSE ''
END ,
类型 = b.name ,
占用字节数 = a.length ,
长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION') ,
小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,
允许空 = CASE WHEN a.isnullable = 1 THEN '√'
ELSE ''
END ,
默认值 = ISNULL(e.text, '') ,
字段说明 = ISNULL(g.[value], '')
FROM syscolumns a
LEFT JOIN systypes b ON a.xusertype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id
AND d.xtype = 'U'
AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = G.major_id
AND a.colid = g.minor_id
LEFT JOIN sys.extended_properties f ON d.id = f.major_id
AND f.minor_id = 0
WHERE d.name = 'tablename' --如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询说有的表信息
ORDER BY a.id ,
a.colorder
