Posted by Rajesh Setty on Wednesday, October 12, 2011
select 'DBCC Show_Statistics ('''+obje.name+''','''+ind.name+''')' from sysindexes ind
Cross Apply(select name from sys.objects obj where obj.object_id = ind.id and obj.type not like 'S' ) obje
Create Query for all available indexes in table. Execute result set to check Stats on index. Stats update on index in very important for performance of table to retrieve data. Technical: Every time any DML Operation is performed, Index structure is impacted. So it's prefered to update Stats in regular interval of Time. Note: Having lot many indexes on table implacts DML operations.
use master
Go
sp_msforeachdb 'Use ?;Select ''?'' as DatabaseName,* from sys.objects where name like ''DateToInt'''
This query helps to find all database for function name.
use master
go
sp_msforeachdb 'Use ?;Select ''?'',* from information_schema.tables where table_name like ''nrmSalesActivations'''
Search all database for table name.
use master
go
sp_msforeachdb 'Use ?;Select ''?'',Table_name,Column_Name from information_schema.columns where table_name like ''adate'''
Search all database for column names.
SELECT name AS "Name",
is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO
| All tables in all database for which autocreate stats and update stats are enabled. | | | | | | | | | | |
