To examine all the properties of a database is time consuming. The following script gives you a quick overview of the most important properties of all the databases on your SQL Server.
CREATE TABLE #TempTable
(
DatabaseName VARCHAR(128)
,DBidINT
,DBSizeVARCHAR(128)
,Recovery VARCHAR(128)
,OwnerVARCHAR(128)
,Status VARCHAR(128)
,Updateability VARCHAR(128)
,UserAccess VARCHAR(128)
,CompatibilityLevelVARCHAR(30)
,LastFullBackupDATETIME
,LastDiffBackupDATETIME
,LastLogBackupDATETIME
,MaintenancePlanVARCHAR(128)
,Name1VARCHAR(128)
,FileName1VARCHAR(128)
,FileGroup1VARCHAR(128)
,Size1VARCHAR(128)
,MaxSize1VARCHAR(128)
,Growth1VARCHAR(128)
,Usage1VARCHAR(30)
,Name2VARCHAR(128)
,FileName2VARCHAR(128)
,FileGroup2VARCHAR(128)
,Size2VARCHAR(128)
,MaxSize2VARCHAR(128)
,Growth2VARCHAR(128)
,Usage2VARCHAR(30)
,CreationDateDATETIME
,SQLSortOrder INT
,Collation VARCHAR(128)
,Version VARCHAR(128)
,IsAnsiNullDefault BIT
,IsAnsiNullsEnabled BIT
,IsAnsiPaddingEnabled BIT
,IsAnsiWarningsEnabled BIT
,IsArithmeticAbortEnabled BIT
,IsAutoClose BIT
,IsAutoCreateStatistics BIT
,IsAutoShrink BIT
,IsAutoUpdateStatistics BIT
,IsCloseCursorsOnCommitEnabled BIT
,IsFulltextEnabled BIT
,IsInStandBy BIT
,IsLocalCursorsDefault BIT
,IsMergePublished BIT
,IsNullConcat BIT
,IsNumericRoundAbortEnabled BIT
,IsPublished BIT
,IsQuotedIdentifiersEnabled BIT
,IsRecursiveTriggersEnabled BIT
,IsSubscribed BIT
,IsTornPageDetectionEnabled BIT
)
DECLARE @databasename VARCHAR(128)
DECLARE @owner VARCHAR(128)
DECLARE @DBidINT
DECLARE @cmptlevel VARCHAR(30)
DECLARE@crdate DATETIME
DECLARE @low NVARCHAR(11)
DECLARE @sql VARCHAR(1000)
SELECT @low = convert(varchar(11),low) from master.dbo.spt_values where type = N'E' and number = 1
DECLARE dbname CURSOR FOR SELECT name,dbid,cmptlevel,crdate,suser_sname(sid) FROM master..sysdatabases
OPEN dbname
FETCH NEXT FROM dbname
INTO @databasename,@DBid,@cmptlevel,@crdate,@owner
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TempTable SELECT @databasename
, @dbid
, 'DBSize'
, CAST( databasePROPERTYEX( @databasename , 'Recovery') AS VARCHAR(128))
, @owner
, CAST( databasePROPERTYEX( @databasename , 'Status') AS VARCHAR(128))
, CAST( databasePROPERTYEX( @databasename , 'Updateability') AS VARCHAR(128))
, CAST( databasePROPERTYEX( @databasename , 'UserAccess') AS VARCHAR(128))
, @cmptlevel
, GETDATE()
, GETDATE()
, GETDATE()
, 'MaintenancePlan'
, 'name1'
, 'filename1'
, 'filegroup1'
, 'size1'
, 'maxsize1'
, 'growth1'
, 'usage1'
, 'name2'
, 'filename2'
, 'filegroup2'
, 'size2'
, 'maxsize2'
, 'growth2'
, 'usage2'
, @crdate
, CAST( databasePROPERTYEX( @databasename , 'SQLSortOrder') AS INT)
, CAST( databasePROPERTYEX( @databasename , 'Collation') AS VARCHAR(128))
, CAST( databasePROPERTYEX( @databasename , 'Version') AS VARCHAR(128))
, CAST( databasePROPERTYEX( @databasename , 'IsAnsiNullDefault') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsAnsiNullsEnabled') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsAnsiPaddingEnabled') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsAnsiWarningsEnabled') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsArithmeticAbortEnabled') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsAutoClose') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsAutoCreateStatistics') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsAutoShrink') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsAutoUpdateStatistics') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsCloseCursorsOnCommitEnabled') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsFulltextEnabled') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsInStandBy') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsLocalCursorsDefault') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsMergePublished') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsNullConcat') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsNumericRoundAbortEnabled') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsPublished') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsQuotedIdentifiersEnabled') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsRecursiveTriggersEnabled') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsSubscribed') AS BIT)
, CAST( databasePROPERTYEX( @databasename , 'IsTornPageDetectionEnabled') AS BIT)
SELECT @sql= 'UPDATE #TempTable set DBSize = (select str(convert(dec(15),sum(size))* ' + @low +
'/ 1048576,10,2)+ N'' MB'' from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set name1 = (select name from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=1) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set name2 = (select name from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=2) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set filename1 = (select filename from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=1) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set filename2 = (select filename from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=2) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set filegroup1 = (select filegroup_name(groupid) from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=1) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set filegroup2 = (select filegroup_name(groupid) from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=2) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set size1 = (select convert(nvarchar(15), size * 8) + N'' KB'' from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=1) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set size2 = (select convert(nvarchar(15), size * 8) + N'' KB'' from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=2) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set maxsize1 = (select (case maxsize when -1 then N''Unlimited'' else
convert(nvarchar(15), maxsize * 8) + N'' KB'' end) from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=1) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set maxsize2 = (select (case maxsize when -1 then N''Unlimited'' else
convert(nvarchar(15), maxsize * 8) + N'' KB'' end) from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=2) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set growth1 = (select (case status & 0x100000 when 0x100000 then
convert(nvarchar(3), growth) + N''%'' else
convert(nvarchar(15), growth * 8) + N'' KB'' end) from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=1) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set growth2 = (select (case status & 0x100000 when 0x100000 then
convert(nvarchar(3), growth) + N''%'' else
convert(nvarchar(15), growth * 8) + N'' KB'' end) from ' + quotename(@databasename, N'[') +
N'.dbo.sysfiles WHERE fileid=2) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set usage1 = (select (case status & 0x40 when 0x40 then ''log only'' else ''data only'' end) from ' +
quotename(@databasename, N'[') + N'.dbo.sysfiles WHERE fileid=1) WHERE dbid=' +
cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set usage2 = (select (case status & 0x40 when 0x40 then ''log only'' else ''data only'' end) from ' +
quotename(@databasename, N'[') + N'.dbo.sysfiles WHERE fileid=2) WHERE dbid=' +
cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set lastfullbackup = (select TOP 1 backup_finish_date from msdb..backupset where type = ''D'' and database_name =''' +
@databasename + ''' order by backup_finish_date desc) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set lastdiffbackup = (select TOP 1 backup_finish_date from msdb..backupset where type = ''I'' and database_name =''' +
@databasename + ''' order by backup_finish_date desc) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
SELECT @sql= 'UPDATE #TempTable set lastlogbackup = (select TOP 1 backup_finish_date from msdb..backupset where type = ''L'' and database_name =''' +
@databasename + ''' order by backup_finish_date desc) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
IF @databasename NOT IN ('master','msdb','model')
BEGIN
SELECT @sql= 'UPDATE #TempTable set maintenanceplan = (select p.plan_name from msdb..sysdbmaintplans p, msdb..sysdbmaintplan_databases d where (d.database_name = ''All Databases'' or d.database_name = ''All User Databases'' or d.database_name = ''' + @databasename + ''') and (p.plan_id = d.plan_id)) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
END
ELSE
BEGIN
SELECT @sql= 'UPDATE #TempTable set maintenanceplan = (select p.plan_name from msdb..sysdbmaintplans p, msdb..sysdbmaintplan_databases d where (d.database_name = ''All Databases'' or d.database_name = ''All System Databases'' or d.database_name = ''' + @databasename + ''') and (p.plan_id = d.plan_id)) WHERE dbid=' + cast(@dbid as VARCHAR(30))
EXEC (@sql)
END
FETCH NEXT FROM dbname
INTO @databasename,@dbid,@cmptlevel,@crdate,@owner
END
SELECT * FROM #TempTable ORDER BY DatabaseName
DROP TABLE #TempTable
CLOSE dbname
DEALLOCATE dbname 
When doing bulk data changes it may be beneficial to disable indexes prior to starting the operation. Fortunately, SQL's rich metadata makes this very easy to automate in a robust fashion.
2018-05-18 (first published: 2015-10-19)
4,794 reads
If you think about it carefully, you’ll realise, that index maintenance is necessary and that it makes sense, and that SQL Server is not doing this task automatically according to the book.
2011-08-18
5,492 reads
Far too often, people who are not trained database administrators somehow end up responsible for a database. They lack the proper training and knowledge to maintain their database, and problems begin to develop. Here’s a primer for all those involuntary DBAs who need a crash course in database maintenance best practices.
2008-08-13
6,696 reads
This script will change the owner of the stored procedure to dbo. It must be run in the same database as the stored procedures.
2001-10-28
1,578 reads
Ever wanted to know whether your triggers are enabled or not? The ObjectProperty command allows you to select information about the status of objects in your database, returning 1 for True and 0 for False. The example below determines whether a single trigger is disabled. SELECT ObjectProperty(object_id('Trigger_Name'), 'ExecIsTriggerDisabled') There are a number of other useful […]
2001-10-28
1,200 reads