查表行数据
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY a.name,b.rows DESC
查所有表名
SELECT NAME FROM SYSOBJECTS WHERE TYPE='U'
delete from 表名
se master
declare @databasename varchar(255)
set @databasename='AIS20110115113651'
alter database AIS20110115113651 set Single_user --单用户
--exec sp_dboption @databasename, N'single', N'true' --将目标数据库置为单用户状态 sql08
dbcc checkdb(@databasename,REPAIR_ALLOW_DATA_LOSS)
dbcc checkdb(@databasename,REPAIR_REBUILD)
alter database AIS20110115113651 set multi_user --多用户
-- exec sp_dboption @databasename, N'single', N'false' --将目标数据库置为多用户状态 sql08，12版不存在sp_dboption该存储过程。
计算数据库中各个表的数据量和每行记录所占用空间
CREATE TABLE #tablespaceinfo
(
nameinfo VARCHAR(500) ,
rowsinfo BIGINT ,
reserved VARCHAR(20) ,
datainfo VARCHAR(20) ,
index_size VARCHAR(20) ,
unused VARCHAR(20)
)
DECLARE @tablename VARCHAR(255);
DECLARE Info_cursor CURSOR
FOR
SELECT '[' + [name] + ']'
FROM sys.tables
WHERE type = 'U';
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tablespaceinfo
EXEC sp_spaceused @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
--创建临时表
CREATE TABLE [#tmptb]
(
TableName VARCHAR(50) ,
DataInfo BIGINT ,
RowsInfo BIGINT ,
Spaceperrow AS ( CASE RowsInfo
WHEN 0 THEN 0
ELSE DataInfo / RowsInfo
END ) PERSISTED
)
--插入数据到临时表
INSERT INTO [#tmptb]
( [TableName] ,
[DataInfo] ,
[RowsInfo]
)
SELECT [nameinfo] ,
CAST(REPLACE([datainfo], 'KB', '') AS BIGINT) AS 'datainfo' ,
[rowsinfo]
FROM #tablespaceinfo
ORDER BY CAST(REPLACE(reserved, 'KB', '') AS INT) DESC
--汇总记录
SELECT [tbspinfo].* ,
[tmptb].[Spaceperrow] AS '每行记录大概占用空间（KB）'
FROM [#tablespaceinfo] AS tbspinfo ,
[#tmptb] AS tmptb
WHERE [tbspinfo].[nameinfo] = [tmptb].[TableName]
ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS INT) DESC
DROP TABLE [#tablespaceinfo]
DROP TABLE [#tmptb]
加入收藏
设为首页
