HOME > .NET Framework > Forum > คำสั่ง query ของ sql server show all table/rows/record จะหาจำนวน table และ rows record ทั้งหมด และแสดงจำนวนแถวออกมาดูด้วยครับ
คำสั่ง query ของ sql server show all table/rows/record จะหาจำนวน table และ rows record ทั้งหมด และแสดงจำนวนแถวออกมาดูด้วยครับ
Tag : .NET, Ms SQL Server 2005, Ms SQL Server 2008
Date :
2011-04-20 10:28:38
By :
wanchai
View :
9728
Reply :
4
No. 1
Guest
เจอแล้วครับ
Code
SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name,
SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
Code
select distinct convert(varchar(30),object_name(a.id)) [Table Name], a.rows
from sysindexes a inner join sysobjects b on a.id = b.id
inner join INFORMATION_SCHEMA.TABLES c on c.[TABLE_NAME] = convert(varchar(30),object_name(a.id))
where c.Table_catalog='db-name'
Code
select distinct convert(varchar(30),
object_name(a.id)) [Table Name],
a.rows from sysindexes a inner
join sysobjects b on a.id = b.id
Date :
2011-04-20 10:58:20
By :
wanchai
No. 2
Guest
Code (SQL)
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