SQL server, is there any central table to get meta data of all tables?

Question

I have to query in SQL server about which table is having maximum data and which one is having minimum data.

Please tell me how can i do this..

i am runninf SQL server 2005.

Answer 1

Have a look at sp_spaceused you can run it in a loop over all your tables

If you would like to get space usage for all tables you could run (the undocumented shortcut):

CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 

INSERT #t
EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'',''TRUE'' '

Then search through #t for your results

Answer 2

If you are looking for metadata about the data structures, use:

select * from sys.objects
select * from sys.columns

Otherwise, please clarify.

Answer 3

Here is a query that returns the number of pages in all data structures for each table in current database:

select object_name([object_id]) as obj_name, sum(page_count) as page_num
from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
group by [object_id]
order by sum(page_count) desc

Dynamic management views provide lots of data about the database.