This example lists all the tables, bigger than 10 MB, in a database
CREATE TABLE #temp (name varchar(50),rows int,reserved varchar(50),data varchar(50),index_size varchar(50),unused varchar(50))
DECLARE @tablename varchar(50)
DECLARE tabels_cursor CURSOR FOR SELECT NAME FROM sysobjects WHERE type='U'
OPEN tabels_cursor
FETCH NEXT FROM tabels_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp EXEC sp_spaceused @tablename
FETCH NEXT FROM tabels_cursor INTO @tablename
END
CLOSE tabels_cursor
DEALLOCATE tabels_cursor
SELECT * FROM #temp WHERE LEN(reserved)>7
DROP TABLE #temp
Monday, November 21, 2005
Howto list large tables in a database
Posted by Goksel Misirli at Monday, November 21, 2005
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment