Automated Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Automated Script

Author

Topic

Frozen_123
Starting Member

33 Posts

Posted - 2007-11-08 : 12:39:35

Hi All,

I have around 80 databases in my one of sql server instance.
we hve to regularly send the database wise health report to the client.
The following fields mentioned by the client in excel sheet

Name of database
Logical file name of .MDF
.Mdf File location drive
Size of the file in MB
Physical file location(full path of .mdf file)
Maxsize
Growth

Now a days we have to check databases one by one.
Could anyone provide me the script which automatically fetch all of required fields information from all of the databases.

Urgent help will be appreciable.

Frozen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-08 : 16:02:56

Just put together what you did in a file, you get your script.

Frozen_123
Starting Member

33 Posts

Posted - 2007-11-08 : 17:53:12

Thanks,
I used the following script...

SET NOCOUNT ON
Create Table #t (
db varchar(255),
filename varchar(255),
name varchar(255),
[size in MB] int,
maxsize int,
growth int)

Declare @db varchar(255)
Declare c Cursor
read_only
for
select name from sys.databases where dbid > 4 and state_desc = 'ONLINE'
Open c
fetch next from c into @db
while @@fetch_status = 0
begin
Exec ('INSERT #t
select ''' + @db + ''', filename, name, (size * 8)/1024, maxsize, growth
from ' + @db + '..sysfiles
where fileid = 1')
fetch next from c into @db
end
Close c
Deallocate c
Select * from #t
Drop table #t

Frozen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources