First stab at a database inventory script

Compatible with sql server 2k5:

USE master
CREATE TABLE #tempresults
(


[name] sysname,
db_size NVARCHAR(13),
[owner] sysname,
[dbid] smallint,
created NVARCHAR(11),
[status] NVARCHAR(600),
compatibility_level tinyint

)
INSERT INTO #tempresults
EXEC sp_helpdb
SELECT db.name, tr.db_size, db.compatibility_level,is_auto_shrink_on, state_desc, recovery_model_desc, page_verify_option_desc

FROM sys.databases db
JOIN #tempresults tr
ON db.name =tr.name

WHERE dbid >4 –This clause will exclude system DBs,master, model, msdb, and tempdb
ORDER BY db.name
DROP TABLE #tempresults

This entry was posted on 22. January 2008 at 19:29 and is filed under T-SQL, SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or trackback from your own site.

| Print