Database Details

This will list out the Database properties of your database in sql server. This is compatible to SQL 2000 and 2005. Before you use the script make sure you have given the database name for the parameter @dbname.

Script

DECLARE @servername VARCHAR(100)
DECLARE @dbname VARCHAR(100)
DECLARE @dbownername VARCHAR(100)
DECLARE @crtdate VARCHAR(100)
DECLARE @totexts INT
DECLARE @usedexts INT
DECLARE @filename VARCHAR(200)
DECLARE @datsumtot NUMERIC(10,2)
DECLARE @datsumused NUMERIC(10,2)
DECLARE @perused NUMERIC(10,2)
DECLARE @logsize NUMERIC(10,2)
DECLARE @logused NUMERIC(10,2)
DECLARE @collation SQL_VARIANT
DECLARE @model SQL_VARIANT
DECLARE @fullbackup VARCHAR(30)
DECLARE @diffbackup VARCHAR(30)
DECLARE @logbackup VARCHAR(30)
DECLARE @filebackup VARCHAR(30)
DECLARE @numusers INT
DECLARE @rcnt INT
SET NOCOUNT ON
CREATE TABLE #filestats(fileid VARCHAR(10),filegroup VARCHAR(10),totextents INT,usedextents INT,name VARCHAR(100), filename VARCHAR(100))
CREATE TABLE #logdetails(databasename VARCHAR(100), logsize NUMERIC(10,4),logused NUMERIC(10,4),status BIT)
SET @dbname = 'Yourdbname'
SELECT @rcnt=COUNT(*) FROM master.dbo.sysdatabases WHERE name LIKE @dbname IF @rcnt>0 BEGIN
SELECT @servername=@@SERVERNAME SELECT @collation=DATABASEPROPERTYEX(@dbname,'collation')
SELECT @model=DATABASEPROPERTYEX(@dbname,'Recovery')
SELECT @dbownername=SUSER_SNAME(sid), @crtdate=CONVERT(VARCHAR(15),crdate,101) FROM master..sysdatabases WHERE name ='tempdb'
INSERT INTO #filestats EXEC('use '+@dbname+' dbcc showfilestats')
INSERT INTO #logdetails EXEC('dbcc sqlperf(logspace)')
PRINT '*****************************SERVER AND DATABASE DETAILS*****************************'
PRINT 'Server Name :'+ @servername
PRINT 'Database Name :'+ @dbname
PRINT 'Database Owner Name :'+ @dbownername
PRINT 'Database Creation Date :'+ @crtdate
PRINT 'Database COllation Name:'+ CONVERT(VARCHAR(100),@collation)
PRINT 'Database Recovery Model:'+ CONVERT(VARCHAR(10),@model)
PRINT '*****************************DATABASE DATA DETAILS*****************************'
PRINT 'File Statistics:(Total Size) (Used Size) (FileName) For Data Files'
DECLARE fil_cursor CURSOR FOR SELECT (totextents*8*8)/1024,(usedextents*8*8)/1024,filename FROM #filestats
OPEN fil_cursor
FETCH NEXT FROM fil_cursor INTO @totexts,@usedexts,@filename
WHILE @@FETCH_STATUS=0
BEGIN
PRINT ' '+CONVERT(VARCHAR(10),@Totexts)+' '+CONVERT(VARCHAR(10),@Usedexts)+' '+@filename
FETCH NEXT FROM fil_cursor INTO @totexts,@usedexts,@filename
END
CLOSE fil_cursor
DEALLOCATE fil_cursor
PRINT 'File % Statistics:(Total Size) (Used Size) (%Used) For Data Files'
SELECT @datsumtot=(SUM(CONVERT(NUMERIC(10,4),totextents))*8*8)/1024, @datsumused=(SUM(CONVERT(NUMERIC(10,4),usedextents))*8*8)/1024, @perused=(@datsumused/@datsumtot)*100 FROM #filestats
PRINT ' '+CONVERT(VARCHAR(10),@datsumtot)+' '+CONVERT(VARCHAR(10),@datsumused)+' '+CONVERT(VARCHAR(10),@perused)
PRINT '*****************************DATABASE LOG DETAILS*****************************'
PRINT 'File % Statistics:(Total Size) (%Used)'
SELECT @logsize=logsize,@logused=logused FROM #logdetails WHERE databasename = @dbname
PRINT ' '+CONVERT(VARCHAR(15),@logsize)+' '+CONVERT(VARCHAR(15),@logused)
SELECT @fullbackup=ISNULL(CONVERT(VARCHAR(30),MAX(backup_finish_date),101),'No Full backup Taken') FROM msdb..backupset WHERE database_name = @dbname AND TYPE ='D'
SELECT @diffbackup=ISNULL(CONVERT(VARCHAR(30),MAX(backup_finish_date),101),'No Diff Backup Taken') FROM msdb..backupset WHERE database_name = @dbname AND TYPE ='I'
SELECT @logbackup=ISNULL(CONVERT(VARCHAR(30),MAX(backup_finish_date),101),'No Log Backup Taken') FROM msdb..backupset WHERE database_name = @dbname AND TYPE ='L'
SELECT @filebackup=ISNULL(CONVERT(VARCHAR(30),MAX(backup_finish_date),101),'No Filegroup Backup Taken') FROM msdb..backupset WHERE database_name = @dbname AND TYPE ='F'
PRINT '*****************************DATABASE BACKUP DETAILS*****************************'
PRINT 'Full Backup Detail for Database :'+@fullbackup
PRINT 'Diff Backup Detail for Database :'+@diffbackup
PRINT 'Log Backup Detail for Database :'+@logbackup
PRINT 'File Group Backup Detail for Database:'+@filebackup
SELECT @numusers = COUNT(*) FROM sysusers WHERE islogin = 1 AND sid IS NOT NULL
PRINT '*****************************DATABASE USER DETAILS*****************************'
PRINT 'Number of Loginable Users:'+CONVERT(VARCHAR(10),@numusers)
DROP TABLE #filestats
DROP TABLE #logdetails
END
ELSE
BEGIN
PRINT 'The database name you have specified is not valid for the Server'
END