InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 09:38:48 GMT20http://www.sqlservercentral.com/Forums/Topic1411011-1550-1.aspx[quote][b]Shadab Shah (1/24/2013)[/b][hr]Antony,Is there any easy way to traverse throught all the database. Because i am finding the above query a little bit of difficcult to understand.[/quote]Might be this is what you are looking for:[code="sql"]IF OBJECT_ID ('tempdb..##DatabaseFileDetails') IS NOT NULLBEGIN DROP TABLE ##DatabaseFileDetails;ENDGOEXECUTE sp_msforeachdb N'USE [?] IF OBJECT_ID (''tempdb..##DatabaseFileDetails'') IS NULL BEGIN SELECT DB_NAME() AS DatabaseName, [file_id] AS FileId, [name] AS [FileName], [type_desc] AS FileType, [state_desc] AS FileState, CAST((([size]*8/1024.0)/1024.0) AS DECIMAL(18,2)) AS FileSizeGB, CAST((((FILEPROPERTY([name],''spaceused'')*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS UsedSpaceGB, CAST(((((size - FILEPROPERTY([name],''spaceused''))*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS FreeSpaceGB, --[size] AS PagesAllocated, --FILEPROPERTY([name],''spaceused'') AS UsedPages, --(size)- (FILEPROPERTY([name],''spaceused'')) AS FreePages, [physical_name] AS FilePath INTO ##DatabaseFileDetails FROM sys.database_files END ELSE INSERT INTO ##DatabaseFileDetails SELECT DB_NAME() AS DatabaseName, [file_id] AS FileId, [name] AS [FileName], [type_desc] AS FileType, [state_desc] AS FileState, CAST((([size]*8/1024.0)/1024.0) AS DECIMAL(18,2)) AS FileSizeGB, CAST((((FILEPROPERTY([name],''spaceused'')*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS UsedSpaceGB, CAST(((((size - FILEPROPERTY([name],''spaceused''))*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS FreeSpaceGB, --[size] AS PagesAllocated, --FILEPROPERTY([name],''spaceused'') AS UsedPages, --(size)- (FILEPROPERTY([name],''spaceused'')) AS FreePages, [physical_name] AS FilePath FROM sys.database_files'GOSELECT * FROM ##DatabaseFileDetailsORDER BY DatabaseName[/code]The query is almost same as Anthony's. The only difference is that I am using sp_msforeachdb to loop through the databases & I am calculating the space in GB instead of MB.Thu, 24 Jan 2013 03:53:00 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic1411011-1550-1.aspxquery sys.master_files, there is a size column which is in pages, so multiply the value by 8, to get size in KB for that file, then sum them up based on the database.Wont get you used space or free space, but will get total spaceThu, 24 Jan 2013 03:36:32 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1411011-1550-1.aspxAntony,Is there any easy way to traverse throught all the database. Because i am finding the above query a little bit of difficcult to understand.Thu, 24 Jan 2013 03:33:12 GMTShadab Shahhttp://www.sqlservercentral.com/Forums/Topic1411011-1550-1.aspxThanks antony. Looping through database is what i was looking for . Once again thanks.Thu, 24 Jan 2013 03:24:54 GMTShadab Shahhttp://www.sqlservercentral.com/Forums/Topic1411011-1550-1.aspx[code="sql"]DECLARE @SQL NVARCHAR(MAX)SELECT @SQL = REPLACE( CAST( ( SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) + --PUT WHAT YOU WANT TO DO IN EACH DATABASE IN THIS BLOCK ---- 'SELECT ' + CHAR(13) + CHAR(10) + 'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) + 'a.FILEID, ' + CHAR(13) + CHAR(10) + '[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) + '[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) + '[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) + 'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) + 'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10) ---- FROM sys.databases FOR XML PATH('') ) AS NVARCHAR(MAX) ), '&#x 0D;',CHAR(13) + CHAR(10) --REMOVE THE SPACE ON THIS LINE BEFORE RUNNING ) --SELECT @SQLEXECUTE sp_executesql @SQL[/code]Remove the space between x and 0 in the string in the code '&#x 0D;'This will loop through the databases, getting file size, used space, free space for each DB file.Thu, 24 Jan 2013 02:53:03 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1411011-1550-1.aspxHi all experts,I am a newbie.I am trying to write a query which would give me the server wise level of the space unused by all the database. For doing this task i search in google and found out [code="sql"]EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'[/code]Which work like a gem. I tried to get the output of the above query in an temp table as[code="sql"]CREATE TABLE #EXECPLAN(database_name NVARCHAR(1000),database_size nvarchar(100),unallocated_space nvarchar(100),reserved nvarchar(100),data nvarchar(100),index_size nvarchar(100),unused nvarchar(100)) INSERT INTO #EXECPLAN EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused' SELECT * FROM #EXECPLAN[/code]The above query is not working because sp_spaceused returns 2 select statement am i am not able to figure it out how , i can insert the output of this 2 select statement in a temp tableThu, 24 Jan 2013 02:50:19 GMTShadab Shah