Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130

    Unanswered: data for all tables in a db

    hi all i am a new bee in sql server 2005 i want to know the data of the tables in a db
    exec sp_spaceused
    this command will give the data of a db if we give the table name then it will show the table data my question is how can i get full data of tables in db

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sp_helpdb 'enter your database name here'
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    tnks for considering
    i have tried this but i get normal information i,e the DB size (details) i want the details of each and every tables

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    exec sp_msforeachtable'
    exec sp_spaceused ''?''
    '
    
    You can also create a temp table and pipe the output from the above snippet into it.
    Last edited by rdjabarov; 07-20-10 at 11:11. Reason: added explanation
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is this any good?
    Code:
    SELECT name
         , rows
         , Convert(varchar(11), Convert(int, Round(reservedpages * 8, 0))) + ' KB' As reserved
         , Convert(varchar(11), Convert(int, Round(pages         * 8, 0))) + ' KB' As data
         , Convert(varchar(11), Convert(int, Round(CASE WHEN usedpages     > pages     THEN (usedpages     - pages    ) ELSE 0 END * 8, 0))) + ' KB' AS index_size
         , Convert(varchar(11), Convert(int, Round(CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END * 8, 0))) + ' KB' As unused
    FROM   (
            SELECT o.name
                 , Sum(CASE WHEN s.index_id < 2
    		                 THEN s.row_count
                         ELSE 0
                       END) As rows
                 , Sum(s.reserved_page_count) As reservedpages
                 , Sum(s.used_page_count) As usedpages
    	           , Sum(CASE WHEN s.index_id < 2
    	                   THEN s.in_row_data_page_count + s.lob_used_page_count + s.row_overflow_used_page_count
    			               ELSE s.lob_used_page_count + s.row_overflow_used_page_count
    		               END) As pages
            FROM   sys.objects As o
             INNER
              JOIN sys.dm_db_partition_stats As s
                ON o.object_id = s.object_id
             LEFT
              JOIN sys.internal_tables As it
                ON it.object_id = s.object_id
               AND it.internal_type IN (202, 204)
            WHERE  o.is_ms_shipped = 0
            GROUP
                BY o.name
           ) As x
    Last edited by gvee; 07-20-10 at 11:17. Reason: added is_ms_shipped filter
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130

    @rdjabarov

    this Sp_ is not applicable when the Db has more than 100 tables

  7. #7
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    @ george
    this code is very nice & helps by showing each table in default view tnks to both of you for considering my query

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by mathukumali View Post
    this Sp_ is not applicable when the Db has more than 100 tables
    Really? Never heard of such limitation. Just ran it against a 8500+ tables database, and it took less than 30 seconds.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by rdjabarov View Post
    Really? Never heard of such limitation. Just ran it against a 8500+ tables database, and it took less than 30 seconds.
    may it has some prb with my Db but the result shows me that the limit exceeds

  10. #10
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    try this
    declare @tblTableUsage table
    (
    [Name] varchar(255),
    [Rows] int,
    [Reserved] varchar(100),
    [Data] varchar(100),
    [Index_size] varchar(100),
    [Unused] varchar(100)
    )

    declare @tblName varchar(255)

    declare cur cursor FAST_FORWARD FOR
    select [name] from sys.tables where type = 'U'
    order by [name];

    OPEN cur;

    FETCH NEXT FROM cur INTO @tblName;

    WHILE @@fetch_status = 0
    BEGIN

    insert into @tblTableUsage
    execute sp_spaceused @objName = @tblName;

    FETCH NEXT FROM cur INTO @tblName;
    END

    CLOSE cur;

    DEALLOCATE cur;


    select * from @tblTableUsage

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by mathukumali View Post
    may it has some prb with my Db but the result shows me that the limit exceeds
    There is nothing wrong with your db, but rather with the way you're running it. As I mentioned, you need to pipe the results of the code into a temp table, rather then just running it as is.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...oh, didn't see your post. How about you try this:
    Code:
    create table #tblTableUsage
    (
    [Name] varchar(255),
    [Rows] int,
    [Reserved] varchar(100),
    [Data] varchar(100),
    [Index_size] varchar(100),
    [Unused] varchar(100)
    )
    insert #tblTableUsage
       exec sp_msforeachtable '
          exec sp_spaceused ''?''
       '
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just so you guys know, the query I have written is based off of the sp_spaceused sproc in SQL2005 - no need for any steenkin' loops or temp tables
    George
    Home | Blog

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Sure, but there is a need for elevated permissions. And if it's "based" on sp_spaceused, why don''t you just use sp_spaceused?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The reason I did this is to suit the requirement stated; all objects, not just the one.

    And I concede that it does require elevated perimssions, but I can't see much of a need for anyone other than a DBA wanting to query the space used for tables, can you?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •