Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Location
    Peg City
    Posts
    5

    Cool Unanswered: List record counts of all tables?

    Hi All,

    Is there a fancy way to list all table names with record counts?
    Using table: INFORMATION_SCHEMA.TABLES

    Also, Is there a way to initialize/empty all data from all tables?

    Thank you very much

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Use TRUNCATE to clear out a table

    as for space

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    GO
    
    CREATE TABLE #SpaceUsed (
    			  [name] 	varchar(255)
    			, [rows] 	varchar(25)
    			, [reserved] 	varchar(25)
    			, [data] 	varchar(25)
    			, [index_size] 	varchar(25)
    			, [unused] 	varchar(25)
    )
    GO
    
    DECLARE   @tablename nvarchar(128)
    	, @maxtablename nvarchar(128)
    	, @cmd nvarchar(1000) 
     SELECT   @tablename = ''
    	, @maxtablename = MAX(name) 
       FROM   sysobjects 
      WHERE   xtype='u'
    
    WHILE @tablename < @maxtablename 
    	BEGIN
    		SELECT @tablename = MIN(name) 
    		  FROM sysobjects 
    		 WHERE xtype='u' and name > @tablename
    	 
    		   SET @cmd='exec sp_spaceused['+@tablename+']'
    		INSERT INTO #SpaceUsed EXEC sp_executesql @cmd
    	END
    
    SET NOCOUNT OFF
    GO
    
    SELECT * FROM #SpaceUsed
    GO
    
    DROP TABLE #SpaceUSed
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2004
    Location
    Peg City
    Posts
    5

    Thumbs up merci

    I didn't mean that fancy! It worked nonetheless.

    Thanks a million

Posting Permissions

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