March 10, 2014 at 9:59 am
I need a query that lists all the tables in all the databases which consumes more space , i need to analayse on this
March 10, 2014 at 10:10 am
Consumes more space than what?
If you want a list of the tables and their sizes, have a look at the sys.dm_db_partition_stats DMV.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2014 at 10:13 am
I can give you the start:-). The query bellow (I didn't write it, but I can't give the credit because I don't remember where I got it) shows you the space that each table uses (with out the space of none clustered indexes). You just need to write something that will make it run on all databases and not just the one that your are connected to.
SELECT schema_name(schema_id) + o.name as TableName,
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * 8192) / 1024)/1024)) AS TotalSpaceUsedInMB,
f.name As FileGroupName
FROM sys.sysindexes i (NOLOCK) INNER JOIN sys.objects o (NOLOCK) ON i.id = o.object_id INNER JOIN sys.filegroups f ON i.groupid = f.data_space_id
WHERE indid IN (0, 1, 255)
AND i.groupid = f.data_space_id
GROUP BY o.schema_id, o.name, f.name
HAVING (((CONVERT(numeric(15,2),SUM(i.reserved)) * 8192) / 1024)/1024) > 1000
ORDER BY TotalSpaceUsedInMB DESC
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 10, 2014 at 10:13 am
Try the script here.
http://jasonbrimhall.info/2011/11/17/table-space-revised-again/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply