April 2, 2010 at 12:58 pm
Hi,
I'm using the below script to get the table size in each database. But when I execute the script, I'm getting the below error:
Msg 8134, Level 16, State 1, Line 63
Divide by zero error encountered.
Please advice what I'm missing here...txtPost_CommentEmoticon(':hehe:');
DECLARE @DBName varchar(255)
DECLARE @SQL nvarchar(4000)
DECLARE @SQL2 nvarchar(4000)
DECLARE DBList CURSOR FOR
SELECT name AS DBName FROM master.dbo.Sysdatabases WHERE dbid > '6'
ORDER BY DBName
OPEN DBList
FETCH NEXT FROM DBList INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'use ' + @DBName + '
DECLARE @dbsize BIGINT
DECLARE @logsize BIGINT
/*****************************************
* Get the database and log file sizes
*****************************************/
SELECT @dbsize = size * 8 / 1024
FROM dbo.sysfiles
WHERE (status & 64 = 0)
SELECT @logsize = size * 8 / 1024
FROM dbo.sysfiles
WHERE (status & 64 <> 0)
SELECT db_name() as ''Database Name'', CAST(@dbsize AS VARCHAR(50)) + '' MB'' AS ''Database Size'', CAST(@logsize AS VARCHAR(50)) + '' MB'' AS ''Log File Size''
'
SET @SQL2 = 'use ' + @DBName + '
DECLARE @table VARCHAR(50)
DECLARE @TableNameVARCHAR(255)
DECLARE @est_rows INT
DECLARE @est_unit CHAR(2)
/*****************************************
* Create cursor to retrieve table names
*****************************************/
DECLARE TableList CURSOR FOR
SELECT Table_Name FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE=''BASE TABLE'' AND TABLE_NAME <> ''dtproperties''
OPEN TableList
FETCH NEXT FROM TableList INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table = @TableName
SET @est_rows = 1
SET @est_unit = ''KB'' -- KB, MB or GB
/*****************************************
* Create temporary table for table size data
*****************************************/
CREATE TABLE #tspace
([name]VARCHAR(100),
[rows]INT,
[rowsize] FLOAT,
[est_size] FLOAT,
[reserved] VARCHAR(50),
[data]VARCHAR(50),
[index_size] VARCHAR(50),
[unused] VARCHAR(50))
/*****************************************
* Generate actual and estimated space
*****************************************/
-- Get current table space data
INSERT INTO #tspace ([name],[rows],[reserved],[data],[index_size],[unused]) EXEC sp_spaceused @table
-- Calculate average row size
UPDATE #tspace
SET [rowsize] = CAST(LEFT([data],CHARINDEX('' '',[data])) AS FLOAT)/[rows]
-- Calculate size for estimated number of rows
IF @est_unit = ''MB''
UPDATE #tspace SET [est_size] = ROUND(([rowsize] * @est_rows)/1024,2)
ELSE IF @est_unit = ''GB''
UPDATE #tspace SET [est_size] = ROUND((([rowsize] * @est_rows)/1024)/1024,2)
ELSE
UPDATE #tspace SET [est_size] = ROUND([rowsize] * @est_rows,2)
/*****************************************
* Output estimated size
*****************************************/
SELECT [Data], [Value] FROM
(SELECT 7 AS [ord], ''Estimated Space'' AS [Data],
CAST([est_size] AS VARCHAR) + '' '' + @est_unit AS [Value] FROM #tspace
UNION
SELECT 6 AS [ord], ''Estimated Row Count'' AS [Data],
CAST(@est_rows AS VARCHAR) AS [Value] FROM #tspace
UNION
SELECT 5 AS [ord], ''Average Row Space'' AS [Data],
CAST([rowsize] AS VARCHAR) AS [Value] FROM #tspace
UNION
SELECT 4 AS [ord], ''Reserved Space'' AS [Data],
[reserved] AS [Value] FROM #tspace
UNION
SELECT 3 AS [ord], ''Current Actual Space'' AS [Data],
[data] AS [Value] FROM #tspace
UNION
SELECT 2 AS [ord], ''Current Row Count'' AS [Data],
CAST([rows] AS VARCHAR) AS [Value] FROM #tspace
UNION
SELECT 1 AS [ord], ''Table Name'' AS [Data],
[name] AS [Value] FROM #tspace) a
ORDER BY [ord]
DROP TABLE #tspace
FETCH NEXT FROM TableList INTO @TableName
END
CLOSE TableList
DEALLOCATE TableList
'
EXEC(@SQL + @SQL2)
FETCH NEXT FROM DBList INTO @DBName
END
CLOSE DBList
DEALLOCATE DBList
April 2, 2010 at 1:24 pm
Just an example"
SELECT db_name() as ''Database Name'', CAST(@dbsize AS VARCHAR(50)) + '' MB'' AS ''Database Size'', CAST(@logsize AS VARCHAR(50)) + '' MB'' AS ''Log File Size''
'
If some one was to attempt to assist you ... editing what you have posted will just take too long, eliminating the double ''
Post your code directly from SSMS using the IF Code Shortcuts for T-SQL ( 8th line down in the listing) this might get you assistance in a reasonable time frame.
This is your code posted using the correct format:
SELECT @dbsize = size * 8 / 1024
FROM dbo.sysfiles
WHERE (status & 64 = 0)
SELECT @logsize = size * 8 / 1024
FROM dbo.sysfiles
WHERE (status & 64 <> 0)
SELECT db_name() as 'Database Name', CAST(@dbsize AS VARCHAR(50)) + ' MB' AS 'Database Size', CAST(@logsize AS VARCHAR(50)) + ' MB' AS 'Log File Size'
April 2, 2010 at 1:37 pm
To reiterate what Ron has said, please remove the double quotes, and format the code using the code link.
Even though the double quotes will work around column alias' like that, it is preferential and better code formatting to not use them.
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
April 2, 2010 at 2:14 pm
Another item you should research ... search here on SSC for
sp_foreachdb and sp_foreachtable -- understand them and you can eliminate your use of cursors.
April 3, 2010 at 3:42 am
bitbucket-25253 (4/2/2010)
Another item you should research ... search here on SSC for sp_msforeachdb and sp_msforeachtable -- understand them and you can eliminate your use of cursors.
Hey Ron,
Have you every looked inside those procedures? Can you guess what they use to loop with?
Hint: it's a cursor 😀
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 7:42 am
Paul White NZ
Yes I am well aware of what sp_foreach uses .... however I am also aware of a remark by Brian Knight on 2001/05/22 at:
http://www.sqlservercentral.com/articles/Advanced+Querying/sp_msforeachtable/181/
Hidden in the depths of the master database are a series of stored procedures that can replace some cursors with these one-liners. Behind the scenes, cursors are still used, but they will save you tons of development time.
Emphasis added by this poster.
April 3, 2010 at 7:50 am
bitbucket-25253 (4/3/2010)
Yes I am well aware of what sp_foreach uses
:ermm:...ok! Many people aren't 😉
They can be useful yes, my comment was tongue-in-cheek 😛
Shame they're undocumented, though, right?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 6, 2010 at 12:54 pm
"tons of development time"?
SELECT name
INTO #db
FROM sys.databases
WHERE
name NOT IN ('master', 'model', 'msdb', 'tempdb', 'admindb') AND
name NOT LIKE '%ReportServer%' AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
SELECT @rc = 1, @dbName = MIN(name)
FROM #db
WHILE @rc <> 0
BEGIN
---- do what you need to do with @dbName
SELECT TOP 1 @dbName = name
FROM #db
WHERE name > @dbName
ORDER BY name
SET @rc = @@ROWCOUNT
END
DROP TABLE #db
Jason
http://dbace.us
😛
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply