这条语句很复杂,返回大小为K
select sysTableTemp.UsersName + '.' + sysTableTemp.ObjectsName as CompleteName, sysTableTemp.IndexName, sysTableTemp.rows, case when sysTableTemp.indid = 1 Then 1 Else 0 End as IsClusteredIndex, (case when sysTableTemp.indid > 1 and sysTableTemp.indid <> 255 Then pageTableTemp.PageSize * sysTableTemp.NonClusteredDataUsed end) as NonClusteredDataUsed, (pageTableTemp.PageSize * (isnull(sysTableTemp.AllData, 0))) As DataSizeUsed, ( case when sysTableTemp.indid = 1 Then pageTableTemp.PageSize * (isnull(sysTableTemp.IndexSizeUsed, 0)- isnull(sysTableTemp.DataSizeUsed, 0)) end) AS ClustedDataUsed from ( select v.low / 1024 as PageSize from master..spt_values v where v.number=1 and v.type=N'E' ) as pageTableTemp, ( select sysindexes.indid, sysindexes.name as IndexName, sysobjects.name as ObjectsName, sysusers.name as UsersName, sysindexes.used as NonClusteredDataUsed, tempTable.DataSizeUsed, tempTable.IndexSizeUsed, tempTable.rows, tempTable.AllData from sysindexes, sysobjects, sysusers, ( select id, sum(case indid when 0 then sysindexes.dpages when 1 then sysindexes.dpages when 255 then isnull(sysindexes.used, 0) end) as DataSizeUsed, sum(case indid when 0 then isnull(sysindexes.used, 0) when 1 then isnull(sysindexes.used, 0) when 255 then isnull(sysindexes.used, 0) end) as IndexSizeUsed, sum(case indid when 0 then convert(int, rows) when 1 then convert(int, rows) end) as rows, sum(case when indid <= 0 then sysindexes.dpages + isnull(sysindexes.used, 0) else isnull(sysindexes.used, 0) end) as AllData from sysindexes group by sysindexes.id ) as tempTable where sysindexes.id = sysobjects.id and sysusers.uid = sysobjects.uid and tempTable.id = sysindexes.id and sysobjects.name not like '#%' and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1 and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0 ) as sysTableTemp order by CompleteName, IsClusteredIndex DESC
select sysTableTemp.UsersName + '.' + sysTableTemp.ObjectsName as CompleteName, sysTableTemp.IndexName, sysTableTemp.rows, case when sysTableTemp.indid = 1 Then 1 Else 0 End as IsClusteredIndex, (case when sysTableTemp.indid > 1 and sysTableTemp.indid <> 255 Then pageTableTemp.PageSize * sysTableTemp.NonClusteredDataUsed end) as NonClusteredDataUsed, (pageTableTemp.PageSize * (isnull(sysTableTemp.AllData, 0))) As DataSizeUsed, ( case when sysTableTemp.indid = 1 Then pageTableTemp.PageSize * (isnull(sysTableTemp.IndexSizeUsed, 0)- isnull(sysTableTemp.DataSizeUsed, 0)) end) AS ClustedDataUsed from ( select v.low / 1024 as PageSize from master..spt_values v where v.number=1 and v.type=N'E' ) as pageTableTemp, ( select sysindexes.indid, sysindexes.name as IndexName, sysobjects.name as ObjectsName, sysusers.name as UsersName, sysindexes.used as NonClusteredDataUsed, tempTable.DataSizeUsed, tempTable.IndexSizeUsed, tempTable.rows, tempTable.AllData from sysindexes, sysobjects, sysusers, ( select id, sum(case indid when 0 then sysindexes.dpages when 1 then sysindexes.dpages when 255 then isnull(sysindexes.used, 0) end) as DataSizeUsed, sum(case indid when 0 then isnull(sysindexes.used, 0) when 1 then isnull(sysindexes.used, 0) when 255 then isnull(sysindexes.used, 0) end) as IndexSizeUsed, sum(case indid when 0 then convert(int, rows) when 1 then convert(int, rows) end) as rows, sum(case when indid <= 0 then sysindexes.dpages + isnull(sysindexes.used, 0) else isnull(sysindexes.used, 0) end) as AllData from sysindexes group by sysindexes.id ) as tempTable where sysindexes.id = sysobjects.id and sysusers.uid = sysobjects.uid and tempTable.id = sysindexes.id and sysobjects.name not like '#%' and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1 and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0 ) as sysTableTemp order by CompleteName, IsClusteredIndex DESC
yong de zhao ?