Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

26Jul/100
Query Table Sizes from a SQL Database

I pulled apart a few stored procedures and ended up with this code. Very nice way to query the row count and size for each table. Take a look at the code, it is simpler than you might think. To use, change the database name at the top of the script to any database you wish to query. I turned this into a really handy stored procedure. When I call the stored procedure I pass the database name. Possibilities are endless.

use master
go

BEGIN try 
DECLARE @table_name VARCHAR(500) ; 
DECLARE @schema_name VARCHAR(500) ; 
DECLARE @tab1 TABLE(
        tablename VARCHAR (500) collate database_default
,       schemaname VARCHAR(500) collate database_default
); 
DECLARE  @temp_table TABLE (    
        tablename sysname
,       row_count INT
,       reserved VARCHAR(50) collate database_default
,       data VARCHAR(50) collate database_default
,       index_size VARCHAR(50) collate database_default
,       unused VARCHAR(50) collate database_default 
); 

INSERT INTO @tab1 
SELECT t1.name
,       t2.name 
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

DECLARE c1 CURSOR FOR 
SELECT t2.name + '.' + t1.name  
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

OPEN c1; 
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0 
BEGIN  
        SET @table_name = REPLACE(@table_name, '[',''); 
        SET @table_name = REPLACE(@table_name, ']',''); 

        -- make sure the object exists before calling sp_spacedused
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
        BEGIN
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
        END
        
        FETCH NEXT FROM c1 INTO @table_name; 
END; 
CLOSE c1; 
DEALLOCATE c1; 
SELECT t1.*
,       t2.schemaname 
FROM @temp_table t1 
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY  schemaname,tablename;
END try 
BEGIN catch 
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname 
END catch

The results look like this:

Looks great.

26May/090
How to Repair a MySQL Database

If a server is shutdown unexpectedly (power outage or hardware failure), one or more MySQL tables can be locked open and may need to be repaired.

Browse to the folder that contains the MySQL databases:

cd /var/lib/mysql/

Check your tables by running the following command:

myisamchk *.MYI

The easiest and safest method is to run the following command (-r means "recovery mode" and -q means "quick recovery mode"). This command will not touch the data within the database:

myisamchk -r -q tbl_name

A more difficult repair to run is as follows. This command will delete damaged data:

myisamchk -r tbl_name

Reference http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html for more information.