Sunday, September 07, 2008
Username: Password: Register
DNN Resources

Current Articles | Categories | Search | Syndication

Thursday, December 06, 2007
TSQL List of table and size in DNN database
By allwebtemplate @ 7:26 PM :: 2177 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
 
AllDnn.FlashTitle
 

click to go back

Home  |  Skin Category  |  Logos  |  Download  |  Support  |  Resources  |  Services  |  FAQ  |  Forum
Copyright (C) 2005-2008 AllDNNSkins.COM Privacy Statement  |  Terms Of Use