Search
DNN Resources Minimize

Current Articles | Categories | Search | Syndication

Thursday, December 06, 2007
TSQL List of table and size in DNN database
By allwebtemplate @ 7:26 PM :: 1211 Views :: 0 Comments :: :: Tips, Knowledges
 

Login as Host, goto Host/SQL, paste the following code and click run.

SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]

Rating
Comments
Currently, there are no comments. Be the first to post one!
Click here to post a comment
  

click to go back

This use Free AllDnnSkins Page Option and Social Bookmark Module. more...
Print this page
Add to Favorites
Email this page
Contact Us
Set as Home
Report Spam/Abuse
AllDnnSkins
Digg
Icio
Google
Facebook
Netscape
Technorati
Yahoo
Gnolia
Blogmarks
Furl
Slashdot