November 17, 2009 at 8:21 am
Greetings all. I am looking for information on the many system oriented views and or xp procs that may be available. Specifically for this question I am looking to determine how many rows are in the tables in my database.
Thanks!!
November 17, 2009 at 3:18 pm
I found this a couple of weeks ago when I was looking at getting the size of my tables (my data file got very large and i didn't know why) - one of the columns returned is number of rows
DECLARE @TableName VARCHAR(100) --For storing values in the cursor
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
--Open the cursor
OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults
SELECT *
FROM #TempTable
ORDER BY dataSize DESC
--Final cleanup!
DROP TABLE #TempTable
credit for this sql doesn't rest with me.. wish I could remember where i got it from
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
November 18, 2009 at 12:34 am
Look in Books Online for the page titled "System Views" (Also available on msdn)
As for counts...
http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply