Post reply

Information requested on system views in 2K5

  • November 17, 2009 at 8:21 am

    #142994

    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

    #1080577

    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

    #1080714

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply