SQL Server db size, name, version query

[JohnnyMCE]

I am working on a query right now that if i run it on a specific server it will tell me the name of all the databases on it as well as size of the database along with version of the particular app. I am stuck on syntax at the moment. Here is what I have. If I run this on the server I am able to return database name and size no problem of all databases on a server.

create table #dbsize (dbname varchar(50), dbsize decimal(10,2))
exec sp_MSforeachdb
'insert into #dbsize select ''?'', (0.0078125 * sum(size)/1024) from ?..sysfiles'
select * from #dbsize
order by dbname
drop table #dbsize

but now I want to include this as well

select AppVersion From dbo.GAppVersion where FK_GApp = 4

if i run that on a particular database it will tell me which version of an app it is running. Problem is I can only run it on each database one at a time. I want to include it in the above query so I can query all teh databases on a server at once. I want to somehow include this query in the above query to include it in the results for some reason either my syntax is incorrect or I am querying it wrong.

 

[JohnnyMCE]

i was able to solve this issue but now I am stuck on another issue. I am trying to find out the size of a particular table that exists on each database on a server to the best of my knowledge this is roughly how it should be but I can't get it to work.

create table #GEventTemp (dbname varchar(50), tablename varchar(100), numberofrows varchar(100), reservedsize varchar(50), datasize varchar(50), indexsize varchar(50), unusedsize varchar(50))
exec sp_MSforeachdb
'insert into #GEventTemp select ''?'', EXEC sp_spaceused N'dbo.GEventLog''
select * from #GEventTemp
order by dbname
drop table #GEventTemp

 

[Kr@n]

You cannot juxtapose a select statement and an exec statement. To keep track of the database name, you could do something like that :

create table #GEventTemp (dbname varchar(50), tablename varchar(100), numberofrows varchar(100), reservedsize varchar(50), datasize varchar(50), indexsize varchar(50), unusedsize varchar(50))
declare @TABLE_NAME varchar(100) set @TABLE_NAME = 'dbo.GEventLog'

declare @SQL varchar(8000)
set @SQL = 'use [?]
if exists(select * from sys.objects where object_id = object_id(N''' + @TABLE_NAME + ''') AND type in (N''U''))
begin
    declare @TEMP_EVENTS table (tablename varchar(100), numberofrows varchar(100), reservedsize varchar(50), datasize varchar(50), indexsize varchar(50), unusedsize varchar(50))
    insert into @TEMP_EVENTS exec sp_spaceused N''' + @TABLE_NAME + '''
    insert into #GEventTemp
    select ''?'', TEMP_EVENTS.* from @TEMP_EVENTS as TEMP_EVENTS
end'

exec sp_MSforeachdb @SQL

select * from #GEventTemp
order by dbname
drop table #GEventTemp

It works well on my SQL2008.

Note : I use a temporary local table (@TEMP_EVENTS to buffer the sp_spaceused result in order to query them later in a trivial SELECT statement which adds the database name to the recordset.

Hope That Helps

 

[JohnnyMCE]

thanks that worked perfectly. I am trying to now mix in some chunks of another query I have in with it. I got my first chunk working but for some reason I can't get the dbsize chunk to work. I know it's got to do with syntax but i'm not sure where the issue is.

create table #GEventTemp (dbname varchar(50), sxversion varchar (10), dbsize decimal (10,2), tablename varchar(100), numberofrows varchar(100), reservedsize varchar(50), datasize varchar(50), indexsize varchar(50), unusedsize varchar(50)) declare @TABLE_NAME varchar(100) set @TABLE_NAME = 'dbo.GEventLog'

declare @SQL varchar(8000)
set @SQL = 'use [?]

if exists(select * from sys.objects where object_id = object_id(N''' + @TABLE_NAME + ''') AND type in (N''U'')) begin
declare @TEMP_EVENTS table (tablename varchar(100), numberofrows varchar(100), reservedsize varchar(50), datasize varchar(50), indexsize varchar(50), unusedsize varchar(50))
insert into @TEMP_EVENTS exec sp_spaceused N''' + @TABLE_NAME + '''
insert into #GEventTemp
select ''?'', (select appversion from ?.dbo.gappversion where fk_gapp = 4), (0.0078125 * sum(size)/1024) from ?..sysfiles, TEMP_EVENTS.* from @TEMP_EVENTS as TEMP_EVENTS end'

exec sp_MSforeachdb @SQL


select * from #GEventTemp
order by dbname
drop table #GEventTemp


the problem is with the line (0.0078125 * sum(size)/1024) from ?..sysfiles this formula by itself can give me dbsize no problem but when i chunk it into here i get errors about 0.0078125

 

[Kr@n]

I think you're missing some select statements in your subqueries. That should be something like that (not tested on my side) :

create table #GEventTemp (dbname varchar(50), sxversion varchar(10), dbsize decimal(10,2), tablename varchar(100), numberofrows varchar(100), reservedsize varchar(50), datasize varchar(50), indexsize varchar(50), unusedsize varchar(50))
declare @TABLE_NAME varchar(100) set @TABLE_NAME = 'dbo.GEventLog'

declare @SQL varchar(8000)
set @SQL = 'use [?]
if exists(select * from sys.objects where object_id = object_id(N''' + @TABLE_NAME + ''') AND type in (N''U''))
begin
    declare @TEMP_EVENTS table (tablename varchar(100), numberofrows varchar(100), reservedsize varchar(50), datasize varchar(50), indexsize varchar(50), unusedsize varchar(50))
    insert into @TEMP_EVENTS exec sp_spaceused N''' + @TABLE_NAME + '''
    insert into #GEventTemp
    select ''?'', (select appversion from ?.dbo.gappversion where fk_gapp = 4), (select 0.0078125 * sum(size)/1024 from ?..sysfiles), TEMP_EVENTS.* from @TEMP_EVENTS as TEMP_EVENTS
end'

exec sp_MSforeachdb @SQL

select * from #GEventTemp
order by dbname
drop table #GEventTemp

Note : if you don't like subqueries much, you can declare some local variable to retain needed info, and simply select them in the last insert into ... select statement.

 

[JohnnyMCE]

that's what i get for staring at sql code at 7am. Can't believe i missed that select. It worked perfectly. Thanks for the help.

 

[Kr@n]

No prob.