Find Last Index Rebuild Time for all Databases on SQL Server

IF EXISTS(SELECT 'X' FROM tempdb..sysobjects where name like '#TABLE_VER%')
drop table #TABLE_VER
CREATE TABLE #TABLE_VER (DBNAME VARCHAR(50), INDEX_DATE DATETIME)

DECLARE @command varchar(1000)

SELECT @command =
'USE [?] INSERT INTO #TABLE_VER SELECT db_name() ,
(SELECT max(STATS_DATE(object_id, index_id)) FROM sys.indexes)'

EXEC sp_MSforeachdb @command

SELECT ' Index Rebuild Date'
UNION
SELECT '**************************************************************'
union
SELECT 'Index Rebuild Ran on '+ DBNAME + ' ON ' + CONVERT(VARCHAR(12),INDEX_DATE) FROM #TABLE_VER WHERE DBNAME NOT IN ('master','tempdb','model','msdb')

Audit Server Configurations

This script will output configuration informations as below:

• Server Version Configurations like SQL Server Edition , Service Pack , Authentication , Clustered/Non Clustered etc.
• Database File Details like Data & Log File Path of System Databases , TempDB , User Databases and the overal size of each databases.
• Memory and sp_configure enabled configurations and also lists the Logical and Physical CPU count.
• Trace flags & Other startup Parameters , TCP Ports , Named Pipes Protocol Information
• Backup details like Last Full backup , last differential backup and last transaction log backup with size , backep up to Tape or Shared Location or Local Drive with path of backup share.

***********************************************************************************

SET NOCOUNT ON

DECLARE @TABLE_VER TABLE (ProductVersion VARCHAR(50),ProductLevel VARCHAR(50),
Edition VARCHAR(100),DotNetVersion VARCHAR(100),Collation VARCHAR(100),
Instance VARCHAR(100),MachineName VARCHAR(100),ServerName VARCHAR(100),
Clustering VARCHAR(50),Authentication VARCHAR(100))

INSERT INTO @TABLE_VER
SELECT
CONVERT(VARCHAR,SERVERPROPERTY('ProductVersion')) AS ProductVersion,
CONVERT(VARCHAR,SERVERPROPERTY('ProductLevel')) AS ProductLevel,
CONVERT(VARCHAR,SERVERPROPERTY('Edition')) AS Edition,
--CASE SERVERPROPERTY('EngineEdition')
--WHEN 1 THEN 'Desktop/Personal'
--WHEN 2 THEN 'Standard'
--WHEN 3 THEN 'Enterprise'
--WHEN 4 THEN 'Express'
--END  EngineEdition,
CONVERT(VARCHAR,SERVERPROPERTY('BuildClrVersion')) AS DotNetVersion,
CONVERT(VARCHAR,SERVERPROPERTY('Collation')) AS Collation,
CONVERT(VARCHAR,SERVERPROPERTY('InstanceName')) as Instance,
CONVERT(VARCHAR,SERVERPROPERTY('MachineName')) AS MachineName,
CONVERT(VARCHAR,SERVERPROPERTY('ServerName')) AS ServerName,
CASE SERVERPROPERTY('IsClustered')
WHEN 1 THEN 'Clustered' ELSE 'Non-Clustered' END AS 'Clustering',
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
ELSE 'Both Windows & SQL' END AS 'Authentication'

SELECT ' Server Version Details'
UNION
SELECT '**************************************************************'
UNION
SELECT '1>ServerName is: ' + ServerName FROM @TABLE_VER
UNION
SELECT '2>MachineName is: ' + MachineName FROM @TABLE_VER
UNION
SELECT '3>Instance is: ' + ISNULL(Instance,'Default') FROM @TABLE_VER
UNION
SELECT '4>ProductVersion is: ' + ProductVersion FROM @TABLE_VER
UNION
SELECT '5>ProductLevel is: ' + ProductLevel FROM @TABLE_VER
UNION
SELECT '6>Edition is: ' + Edition FROM @TABLE_VER
UNION
SELECT '7>DotNetVersion is: ' + DotNetVersion FROM @TABLE_VER
UNION
SELECT '8>Server Collation is: ' + Collation FROM @TABLE_VER
UNION
SELECT '9>Authentication is: ' + Authentication FROM @TABLE_VER
UNION
SELECT '91>Clustering is: ' + Clustering FROM @TABLE_VER

SELECT ' File Details'
UNION
SELECT '**************************************************************'
UNION
SELECT '1>System Databases Data Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) = 'master' AND fileid = 1
UNION
SELECT '2>System Databases Log Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) = 'master' AND fileid = 2
UNION
SELECT '3>TempDB Databases Data Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) = 'tempdb' AND fileid <> 2
UNION
SELECT '4>TempDB Databases Log Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) = 'tempdb' AND fileid = 2
UNION
SELECT '5>User Databases Data Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) not in ('master','msdb','model','tempdb') AND fileid <> 2
UNION
SELECT '6>User Databases Log Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) not in ('master','msdb','model','tempdb') AND fileid = 2

SELECT ' Database Details'
UNION
select 'Database:' + DB_NAME(dbid) +  'Size(in MB) is : ' + CONVERT(VARCHAR,SUM(size/128)) + ' MB' from master..sysaltfiles group by dbid
SELECT '**************************************************************'
UNION
select ' Config & Memory Details'
UNION
SELECT 'Config: ' + convert(varchar,name) + ' | Value: ' + convert(varchar,value_in_use)
from sys.configurations where value_in_use <> 0
and configuration_id not in ( 115,116,117,505,1126,1127,1519,1520,1531,1536,1538,1540,1541,1543,1544,1557,
1563,1565,1567,1568,1573,1575,16387)
UNION
select 'Minimum Memory (in MB): ' + convert(varchar,value_in_use )
from sys.configurations where name in ('min server memory (MB)')
union
select 'Maximum Memory (in MB): ' + convert(varchar,value_in_use )
from sys.configurations where name in ('max server memory (MB)')
union
SELECT 'Logical CPU Count: ' + convert(varchar,cpu_count) + ', Physical CPU Count: '
+ convert(varchar,cpu_count / hyperthread_ratio)
FROM sys.dm_os_sys_info
DECLARE @table_err TABLE (LogDate datetime,Processinfo VARCHAR(50),Params VARCHAR(7000))
DECLARE @I INTEGER
SELECT @I = 0
WHILE @I < 10
BEGIN
 INSERT INTO  @table_err
 EXEC master..xp_readerrorlog @I,1,'Server','Startup'
 INSERT INTO  @table_err
 EXEC master..xp_readerrorlog @I,1,'Server','Server local connection'
 INSERT INTO  @table_err
 EXEC master..xp_readerrorlog @I,1,'Server','listening'

 IF (SELECT COUNT('X') FROM @table_err) > 0
 SELECT @I = 100

 SELECT @I = @I + 1
END

SELECT ' Startup Parameters & Protocol Details'
UNION
SELECT '**************************************************************'
UNION
SELECT Params FROM @table_err

SELECT ' Backup Details'
UNION
SELECT '**************************************************************'
UNION
select 'Last Full Backup of ' + CONVERT(varchar,database_name) + ' happened on ' +
  convert(varchar,backup_finish_date) + '.Size of Backup(in MB) is ' +
  convert(varchar,cast(backup_size/1048576 as DECIMAL(10, 2))) + '.Database Recovery is ' +
  convert(varchar,recovery_model) + char(13) +
  'Backed up to ' + case device_type when 2 then 'Disk' when 5 then 'Tape' when 7 then 'Virtual Device' end
  + '.Backup Path is ' + physical_device_name
from msdb..backupset o , msdb..backupmediafamily f
where backup_finish_date = ( select max(backup_finish_date) from msdb..backupset i
                                          where i.database_name = o.database_name and type = 'D')
and type = 'D' and database_name in ( select name from master..sysdatabases)
and o.media_set_id = f.media_set_id
UNION
select 'Last Differential Backup of ' + CONVERT(varchar,database_name) + ' happened on ' +
  convert(varchar,backup_finish_date) + '.Size of Backup(in MB) is ' +
  convert(varchar,cast(backup_size/1073741824 as DECIMAL(10, 2)))
from msdb..backupset o
where backup_finish_date = ( select max(backup_finish_date) from msdb..backupset i
                                          where i.database_name = o.database_name and type = 'I')
and type = 'I' and database_name in ( select name from master..sysdatabases)
UNION
select 'Last Log Backup of ' + CONVERT(varchar,database_name) + ' happened on ' +
  convert(varchar,backup_finish_date) + '.Size of Backup(in MB) is ' +
  convert(varchar,cast(backup_size/1073741824 as DECIMAL(10, 2)))
from msdb..backupset o
where backup_finish_date = ( select max(backup_finish_date) from msdb..backupset i
                                          where i.database_name = o.database_name and type = 'L')
and type = 'L' and database_name in ( select name from master..sysdatabases)

COUNT(*) Versus IF EXISTS.

This style of coding is often followed by many developers where in we check whether records are present in table by using SELECT * or COUNT(*).But the question is are we using the
right method to instead for just checking existence of records and the answer is no.

For a developer it is nice to see records in table for verification but it would be still very nicer if one has followed the highly efficient EXISTS clause from a performance stand point, which performs many a times better compared to COUNT(*) as the execution chain breaks up as soon it locates a value but in COUNT(*) it scans the entire table to find the count of records which would prove to be costly and not satisfying our need

As always let us see an example to understand this better:

SET NOCOUNT ON
SET STATISTICS IO OFF

IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_COUNT_EXISTS')
DROP TABLE TEST_COUNT_EXISTS
GO

CREATE TABLE TEST_COUNT_EXISTS
( DOC_ID INT IDENTITY(1,1) NOT NULL,
DOC_NAME VARCHAR(255) NOT NULL,
LANG_ID INT NOT NULL,
DOC_PATH VARCHAR(2000) NOT NULL
)

CREATE INDEX IX_LANG_ID ON TEST_COUNT_EXISTS(LANG_ID)

DECLARE @iCount INT
SELECT @iCount = 1

--Populate Table with 100000 rows
WHILE @iCount < = 100000
BEGIN
INSERT INTO TEST_COUNT_EXISTS(DOC_NAME,LANG_ID,DOC_PATH)
SELECT 'DOC_NAME' + CONVERT(VARCHAR,@iCount), 1 , '\\MYCOMP\PATH\' + CONVERT(VARCHAR,@iCount)
SELECT @iCount = @iCount + 1
END

--Make the sample random by using GUID
UPDATE TEST_COUNT_EXISTS
SET LANG_ID = 1
WHERE DOC_ID IN ( SELECT TOP 10000 DOC_ID FROM TEST_COUNT_EXISTS ORDER BY NEWID())

UPDATE TEST_COUNT_EXISTS
SET LANG_ID = 2
WHERE DOC_ID IN ( SELECT TOP 10000 DOC_ID FROM TEST_COUNT_EXISTS ORDER BY NEWID())

UPDATE TEST_COUNT_EXISTS
SET LANG_ID = 3
WHERE DOC_ID IN ( SELECT TOP 10000 DOC_ID FROM TEST_COUNT_EXISTS ORDER BY NEWID())


SET STATISTICS IO ON

IF (SELECT COUNT(*) FROM TEST_COUNT_EXISTS WHERE LANG_ID = 3) > 0
SELECT 'COUNT(*)'

IF EXISTS (SELECT * FROM TEST_COUNT_EXISTS WHERE LANG_ID = 3)
SELECT 'EXISTS'

/* Output
Table 'TEST_COUNT_EXISTS'. Scan count 1, logical reads 141, physical reads 0, read-ahead reads 31, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TEST_COUNT_EXISTS'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

/* Output

By the above you we can infer that its EXISTS performs better when compared to a COUNT(*).