How do i determine the database and log file sizes with a sql query in dbx connector?

[pdjhh]

Hi,

We have the database connector v2 running on a hf talking nicely to a couple of sql servers. There's a couple of sql admin value they want to get from the servers as in database and log file sizes. None of the queries I found on the internet get the value and it looks like that's because the account in use would need a higher level of access that they don't want to provide to that account. It has datareader level access.

I found this solution
use "db_name"
exec sp_spaceused
And that works with this level of access but I have to set one up for each of the mul;tiple databases individually and would require manual intervention when a new database is added. There's supposedly this one but it doesn't work for me:
EXECUTE sp_MSforeachdb 'EXECUTE sp_spaceused'. I get 'the statement did not return a result set'.

So if anyone has any ideas of how to get this infomormation for multiple databases with that level of account access that would be great.
Please doni't advise to install the sql app as it's not compatible with db connector v2 and the advise I was given on a previou post in here was to install the db connector v1 and hf software onto the sql servers themselves and that't not going to happen.

Thanks.

[pdjhh · Accepted solution]

Someone dropped this reply in a sql forum. It works very well with the low level read access I had but, I think because of the way it output its results, I couldn't ingest it using the DB Connector. I had to get the DBA to write a script that wrote the output to a csv file which I sucked in with the UF.

Here it is anyway:

exec sp_msforeachdb '

declare @pages bigint -- Working variable for size calc.
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint

select @dbsize = sum(size)
from [?].sys.database_files
where type = 0

select @logsize = sum(size)
from [?].sys.database_files
where type = 1

select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from [?].sys.partitions p
join [?].sys.allocation_units a
on p.partition_id = a.container_id
left join [?].sys.internal_tables it
on p.object_id = it.object_id

 select ''?'' as databasename,
     @dbsize/128 as database_size_MB, 
     @logsize/128 as log_size_MB, 
     (case when @dbsize >= @reservedpages then (@dbsize-@reservedpages) /128 else 0 end) as unallocated_space_MB, 
     reserved_MB = @reservedpages / 128 ,
     data_MB = @pages / 128 ,
     index_size_MB = (@usedpages - @pages) / 12,
     unused_MB = (@reservedpages - @usedpages) / 128