02.Mssql

1.表字段列名类型查询：

• select a.name as [列名],a.length as [列长度],c.name as [列类型],b.name as [表名] from dbo.syscolumns as a inner join dbo.sysobjects as b on b.id = a.id inner join dbo.systypes as c on a.xtype = c.xtype where c.name<>'sysname' and b.name='POS_TD'

2.数据库大小查询：

• #创建临时表
• create table #tablesize (name varchar(50),rows int,reserved varchar(50), data varchar(50),index_size varchar(50),unused varchar(50))
• #插入数据
• insert into #tablesize (name,rows,reserved,data,index_size,unused) exec sp_msforeachTable @Command1="sp_spaceused '?'"
• #替换掉KB字符
• update #tablesize set data=replace(data,'KB','')
• #查询排序
• select *,convert(int,data) as a from #tablesize order by A desc
• #删除临时表
• drop table #tablesize

3.执行比较慢的语句：

• SELECT TOP 20
• total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
• qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
• last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
• SUBSTRING(qt.text,qs.statement_start_offset/2+1,
• (CASE WHEN qs.statement_end_offset = -1
• THEN DATALENGTH(qt.text)
• ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
• AS [使用CPU的语法], qt.text [完整语法],
• dbname=db_name(qt.dbid),
• object_name(qt.objectid,qt.dbid) ObjectName
• FROM sys.dm_exec_query_stats qs WITH(nolock)
• CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
• WHERE execution_count<1
• ORDER BY total_worker_time DESC

4.死锁：

• #死锁spid查询
• select request_session_id spid,
• OBJECT_NAME(resource_associated_entity_id) tableName
• from sys.dm_tran_locks
• where resource_type='OBJECT'
• #根据spid查询语句
• DBCC INPUTBUFFER(61)