How to list all tables ordered by their size within a SQL Server 2005 database?

Hi, all,

Would please any expert here give me any guidance and advices for how to list all tables sorted by their size within a SQL Server 2005 database?

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Hi,

A similar kind of query I've seen on a site. In the example given by the author of the query, he usedDynamic Management Views. You can find the original query from here http://www.extremeexperts.com/SQL/Scripts/FindSizeOfTable.aspx

As per your requirement, I have made some changes to the query. Here it is

WITH table_space_usage ( schema_name, table_name, used, reserved, ind_rows, tbl_rows )
AS(SELECT
s.Name
, o.Name
, p.used_page_count * 8
, p.reserved_page_count * 8
, p.row_count
,casewhen i.index_id in( 0, 1 )then p.row_count else 0 end
FROMsys.dm_db_partition_stats p
INNERJOINsys.objectsas o ON o.object_id= p.object_id
INNERJOINsys.schemasas s ON s.schema_id = o.schema_id
LEFTOUTERJOINsys.indexesas i on i.object_id= p.object_idand i.index_id = p.index_id
WHERE o.type_desc ='USER_TABLE' and o.is_ms_shipped = 0)

SELECT t.schema_name
, t.table_name
,sum(t.used)as used_in_kb
,sum(t.reserved)as reserved_in_kb
,sum(t.tbl_rows)as rows

FROM table_space_usage as t

GROUPBY t.schema_name , t.table_name

ORDERBY used_in_kb desc

Hope it will help you

|||

Hi, Neeraj,

Thanks a lot for your very helpful guidance.

With best regards,

Yours sincerely,

How to list all table without a primary key within SQL Server 2005 database?

Hi, All,

Would please any expert give me any advice and guidance for how can we list all tables without any primary key within a SQL Server 2005 database?

Thanks a lot in advance for that.

With best regards,

Yours sincerelyd,

Switch to the database you want to analyse and run this script:

select

sys.schemas.nameas [Schema],

sys.tables.nameas [Table]

fromsys.tables

leftouterjoinsys.schemasonsys.schemas.schema_id =sys.tables.schema_id

wheresys.tables.object_idnotin

(

select

sys.indexes.object_id

fromsys.indexes

wheresys.indexes.is_primary_key = 1

)

|||

Hi, Daniel, thanks a lot.

With best regards,

Yours sincerely,

|||

You can also run

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY schema_name, table_name;

Please take a look at the FAQ in msdn2 for catalog views. The FAQ is at http://msdn2.microsoft.com/en-us/library/ms345522.aspx. It answers a lot of catalog view related queries.

|||

Hi, Asvin, Thanks a lot for your kind guidance.

With best regards,

Yours sincerely,

How to list all filegroups for a specific database?

Hi, all here,

Would please any expert here give me any guidance for how to list all filegroups for a specific database within SQL Server 2005?

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Within the database use the following command:

SELECT * FROM sys.filegroups

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

use northwind
select * from sysfiles

select * from sysfilegroups

|||

Hi, Jens and Joeydj, thanks a lot for your guidance.

With best regards,

Yours sincerely,