SQL2000, SQL2005, SQL2008でテーブルサイズを調査するクエリー
テーブル名、行数、データサイズ(MB)


SELECT object_name(id)as 'Table Name' ,rowcnt as 'Number of Rows',
dpages as 'Number of Pages',(dpages * 8 )/1024 'サイズ(MB)'
FROM sysindexes
WHERE indid IN (1,0)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
ORDER BY 'サイズ(MB)' DESC


■SQL2000, SQL2005, SQL2008対応

SQL2000のEnterpriseManagerや2005のManagement Consoleで
現在接続中のユーザーを確認するのと同等のクエリーです。


SELECT
spid,status,hostname,program_name,cmd,cpu
,physical_io,blocked,dbid,convert(sysname, rtrim(loginame)) as loginname
,substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char'
from master.dbo.sysprocesses (nolock)
where hostname <> ''

———————————————————————————
order by cpu desc,physical_io desc
———————————————————————————

SELECT
spid,status,hostname,convert(sysname, rtrim(loginame)) as loginname,
program_name,cmd,cpu,physical_io,blocked,dbid
,substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char'
from master.dbo.sysprocesses (nolock)
where hostname <> ''
order by cpu desc,physical_io desc