SQL 常用排查语句

 查询所有表记录数

use DB go SELECT object_name (i.id) TableName, rows as RowCnt FROM sysindexes i INNER JOIN sysObjects o ON (o.id = i.id AND o.xType = 'U ') WHERE indid < 2 ORDER BY TableName

查询所有表空间

SELECT 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 TotalSpaceMB, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB 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.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows ORDER BY t.Name

收缩数据库

USE DB GO DBCC SHRINKDATABASE(DB)

查询数据库死锁、杀死进程

SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName FROM sys.dm_tran_locks WHERE resource_type='OBJECT ' KILL 75（spid）--杀死进程

修改数据库为多用户访问状态

ALTER DATABASE DB SET SINGLE_USER WITH NO_WAIT ALTER DATABASE DB SET EMERGENCY DBCC checkdb (DB, REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE DB SET online ALTER DATABASE DB SET Multi_USER WITH NO_WAIT

增加字段、修改字段长度类型

use DB go alter table table_test alter column 类型 长度 null; --修改字段长度、类型 alter table table_test add HF varchar(50) null;  --插入字段