CODEWITHSUNDEEP
sql-serversql-server-2005
Amit Kumar Gupta
Amit Kumar Gupta

Reputation: 7413

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

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.

Upvotes: 1

Views: 132

Answers (3)

VladV
VladV

Reputation: 10349

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.

Upvotes: 2

Sam Saffron
Sam Saffron

Reputation: 131112

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

Upvotes: 2

Tim M.
Tim M.

Reputation: 54377

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

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

Otherwise, please clarify.

Upvotes: 2

Related Questions