using sp_helpdb to get size of DB's. (Or another way???)

[moonrox]

Thanks for you help in advance. I am trying to use sp_helpdb to get the size of all the databases on a sql server. The results of sp_helpdb (when using the @dbname='databasename') is 2 sets of data. The first iset s the results I want and the second set is the information about the files. While this second set of information is valid and relevant I don't want the second set for this query. So. Below is my code. You can see that I am parsing the sysdatabases table in the master db to create a temporary table so that I can use the @dataname variable later to feed to sp_helpdb. I can't figure out how to limit sp_helpdb and there might be a different way to do this. So I am asking for help.

FYI my Cursor works fine I just don't like the output with 2 result sets. So I would like to either figure out a different way to get DB size or reduce the result set of the sp_helpdb output.

START TSQL
************************************************** *****************************
DECLARE @fillfactor varchar(2)
DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE @dataname varchar(60)
DECLARE @dataname_header varchar(75)

DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases
WHERE name like ('%SOMEVARIABLE%')

OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
SELECT @dataname = RTRIM(@dataname);
--PRINT @dataname
exec sp_helpdb @dbname=@dataname;
--FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END


DEALLOCATE datanames_cursor

[moonrox]

I need to clarify this request. I have a script that works as a TSQL query that writes to a #tmp table. But I need to port this TSQL Script to SSRS and just copying the script to SSRS Report wizard does not work . I will post the code and the error. The error relates to using a TMP Table. ERROR: There is an error in the query. Invalid object name '#tmp'. I get this from the SSRS Design the query wizard. If I just enter the code and run it from inside visual studio sql server reporting tool it runs fine but I cant upload it to the SSRS Report Server.

 
CREATE TABLE #tmp
(
name varchar(200), 
[size] varchar(20), 
owner varchar(50), 
dbid int, 
created datetime, 
status varchar(4000), 
compat int
) 
INSERT INTO #tmp EXEC sp_helpdb
                                                                                                                  SELECT      *
FROM          #tmp
WHERE      name LIKE '%searchstring%'
ORDER BY size DESC 
 
DROP TABLE #tmp
 

[moonrox]

I found the answer.


Temp Tables Cause SSRS not to Populate Fields
Posted by Adam Rink at 7/19/2007 and is filed under SQL Server Reporting Services - SSRS
If you use stored procedures that contain dynamic SQL or temp tables, you know the frustration with SSRS not detecting the field names automatically for the report. The reason for this is because SSRS does not run the stored procedure to get the output and field names. Instead it uses the SET FMTONLY ON command to return just the metadata.

While sort of annoying when you have a few columns, when you have a ton it makes it a long and tedious task to build the report. The work around is to use text instead of a stored procedure type in your dataset. Then set the FMTONLY setting to off.

SET FMTONLY OFF;
EXEC dbo.mystoredproc

WARNING: This will execute the proc, so if you have some data modification code in the stored proc, then the data will be modified.