Tags

, , , ,


Today I was working on inventory analysis and I needed to find out the database size information across all the servers. Unfortunately few of our servers are still in SQL Server 2000 ( Yeah..I am not Kidding..that’s true !!) and hence I had to come up with a script that would run in all the environment. I had a script in my inventory ( Sorry, I don’t remember the actual author of this tool neither I have the link, if anyone is aware of the actual site then please let me know and I will mention it in my blog)

Below is the script for the same:

CREATE TABLE #db_files (
db_files VARCHAR(300)
,file_loc VARCHAR(300)
,filesizeMB DECIMAL(9, 2)
,spaceUsedMB DECIMAL(9, 2)
,FreespaceMB DECIMAL(9, 2)
)

DECLARE @strSQL NVARCHAR(2000)
DECLARE @dbName VARCHAR(2000)

DECLARE @getDBname CURSOR SET @getDBname = CURSOR
FOR
SELECT NAME
FROM sysdatabases

OPEN @getDBname

FETCH NEXT
FROM @getDBname
INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @strSQL = ‘ use ‘ + @dbName + ‘ INSERT INTO #db_files select name , filename , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB , convert(decimal(12,2),round(fileproperty(a.name,”SpaceUsed”)/128.000,2)) as SpaceUsedMB , convert(decimal(12,2),round((a.size-fileproperty(a.name,”SpaceUsed”))/128.000,2)) as FreeSpaceMB from dbo.sysfiles a ‘

EXEC sp_executesql @strSQL

FETCH NEXT
FROM @getDBname
INTO @dbName
END

CLOSE @getDBname

DEALLOCATE @getDBname
GO
select * from #db_files
Drop Table #db_files

 It will produce the below output:

Image

Hope this will help you.

Cheers,

Subhro Saha

Advertisements