Update Statistics and finding Statistics update date

To find how old in days are statistics for all the Tables and Indexes in AdventureWorks database use this query:
USE AdventureWorks
GO
SELECT OBJECT_NAME(I.object_id) AS Object_Name, I.name AS index_name, STATS_DATE(I.OBJECT_ID, index_id) AS Statistics_Updated_Date ,
DATEDIFF(d,STATS_DATE(I.OBJECT_ID, index_id),getdate()) Days_since_update
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.name IS NOT NULL
ORDER BY DATEDIFF(d,STATS_DATE(I.OBJECT_ID, index_id),getdate()) DESC
To find how old in days are statistics for ‘Person’ Table and Indexes in the table in AdventureWorks database:

USE AdventureWorks
GO
SELECT OBJECT_NAME(I.object_id) AS Object_Name, I.name AS index_name, STATS_DATE(I.OBJECT_ID, index_id) AS Statistics_Updated_Date ,
DATEDIFF(d,STATS_DATE(I.OBJECT_ID, index_id),getdate()) Days_since_update
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.name IS NOT NULL AND T.name=('Person')
ORDER BY DATEDIFF(d,STATS_DATE(I.OBJECT_ID, index_id),getdate()) DESC
To update statistics for ‘Person.Person’ Table with an additional option of FULLSCAN that means that the complete table is scanned to update the statistics:
USE AdventureWorks;
GO
UPDATE STATISTICS Person.Person
WITH FULLSCAN
GO
To update statistics for ‘Person.Person’ Table with an option of SAMPLE 50 PERCENT:
USE AdventureWorks;
GO
UPDATE STATISTICS Person.Person
WITH SAMPLE 50 PERCENT
GO
To update all statistics for the database:
USE AdventureWorks;
GO
EXEC sp_updatestats;
To update statistics for all tables in ‘Person’ schema in AdventureWorks database with an additional option of FULLSCAN:
USE AdventureWorks;
GO
SET NOCOUNT ON
DECLARE @SQLcommand NVARCHAR(512),
@Table SYSNAME
DECLARE Tables_Cursor CURSOR FOR
SELECT table_schema + '.' + table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' and table_schema = 'Person'
OPEN Tables_Cursor
FETCH NEXT FROM Tables_Cursor
INTO @Table
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'UPDATING STATISTICS FOR TABLE: ' + @Table
SET @SQLcommand = 'UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN'
EXEC sp_executesql @SQLcommand
FETCH NEXT FROM Tables_Cursor
INTO @Table
END
CLOSE Tables_Cursor
DEALLOCATE Tables_Cursor
SET NOCOUNT OFF
GO

To update statistics that require updating, using sp_updatestats, in all user databases:
Set Nocount on
Declare databases Cursor For
Select name from master.dbo.sysdatabases where name not in ('master','TempDB', 'msdb', 'model')
Declare @dbname varchar(100)
Declare @SQLcommand nvarchar(200)
Open databases
Fetch Next from databases into @dbname
While @@Fetch_status=0
begin
if @dbname is null
Begin
Print 'null Value'
end
else
Begin
PRINT 'Updating Statistics in ' + @dbname
SELECT @SQLcommand = 'USE ' + @dbname + ' EXEC sp_updatestats'
EXEC(@SQLcommand)
PRINT ''
End
Fetch Next from databases into @dbname
end
Close databases
Deallocate databases
GO

Updating database statistics is an I/O intensive operation. Plan to update statistics when database traffic is at its lightest.