How to export SQL Database properties

[Guvnor]

Hello there,

I have about 30 sql servers runnign multiple databases amd i need to
audit them so what i want to do is to log onto each server and then
maybe export the database properties into an excel sheet or text file.

Anyone know how to do that i am quite new to sql any help will be
awesome.

Regards and thanx in advance

[joe_acme]

Do a select from the sysdatabases table in the master database. All
the database property information is there. You may need to look up
sysdatabases in Books Online to interpret the status column.

Joe

Guvnor wrote:

Hello there,

I have about 30 sql servers runnign multiple databases amd i need to
audit them so what i want to do is to log onto each server and then
maybe export the database properties into an excel sheet or text file.

Anyone know how to do that i am quite new to sql any help will be
awesome.

Regards and thanx in advance

[mladjo]

Hi.

I will suggest to you a solution that isn't the best but it works.
This is "static solution", because you must to have standardized xls
document with columns that you want to export.

I will show you solution without Integration services (you can try with IS).

There are a few steps that you must process:
1. Execute this script to enable requirement for openrowset (export data
to excel with query):
2. Create xls document and put columns into it (the same column like
your statement)
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
exec sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
3. Execute followed script for export data to excel:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\db_properties.xls', 'SELECT name,
create_date, collation_name FROM [Sheet1$]') SELECT name, create_date,
collation_name FROM sys.databases

Once more, you must have the same columns in your query and in predefined
xls document.

Database properties exists in system catalog: sys.databases,
sys.master_files.......

Try to make stored procedure and modify this query as you wish.
Good luck.