First stab at a database inventory script

22. January 2008 by Bennett.

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

Posted in T-SQL, SQL Server | Print | No Comments »