标签 ‘ sql

SQL Server 计算表记录数和占用空间


USE 数据库名字
GO

SELECT obj.name AS TableName, prt.rows AS TotalRows, SUM(alloc.used_pages) * 8 AS [SpaceUsed(KB)]
FROM sys.objects AS obj INNER JOIN
sys.indexes AS idx ON obj.object_id = idx.object_id INNER JOIN
sys.partitions AS prt ON obj.object_id = prt.object_id INNER JOIN
sys.allocation_units AS alloc ON alloc.container_id = prt.partition_id
WHERE (obj.type = 'U') AND (idx.index_id IN (0, 1))
GROUP BY obj.name, prt.rows
ORDER BY TableName

  • SQL Server 计算表记录数和占用空间已关闭评论

清空SQL数据库中所有数据表的数据保留结构

在Mssql2005上测试通过,以备不时之需。

use TestDB_2000_2005_2008  
go  
Declare @t varchar (1024)  
Declare @SQL varchar(2048)  
Declare tbl_cur cursor for  select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' 
OPEN tbl_cur FETCH NEXT  from tbl_cur INTO @t 
WHILE @@FETCH_STATUS = 0  
BEGIN 
SET @SQL='TRUNCATE TABLE '+ @t 
--print (@SQL)  
EXEC (@SQL)  
FETCH NEXT  from tbl_cur INTO @t 
END 
CLOSE tbl_cur  
DEALLOCATE tbl_Cur  
  • 清空SQL数据库中所有数据表的数据保留结构已关闭评论

return top