Lists all the tables in a database that do not contain any LOB columns.
Useful when rebuilding indexes online.
rodg11sma,
2011-08-30 (first published: 2011-08-25)
Lists all the tables in a database that do not contain any LOB columns.
Useful when rebuilding indexes online.
SELECT DISTINCT '['+ic.TABLE_SCHEMA+'].'+'['+ic.TABLE_NAME+']' FROM INFORMATION_SCHEMA.COLUMNS ic Inner Join sys.sysobjects so ON ic.TABLE_NAME = so.name Inner Join sys.sysindexes si ON so.id = si.id where SO.type = 'U' and TABLE_SCHEMA+TABLE_NAME not in (SELECT DISTINCT ic.TABLE_SCHEMA+ic.Table_Name FROM INFORMATION_SCHEMA.COLUMNS ic Inner Join sys.sysobjects so ON ic.Table_Name = so.name Inner Join sys.sysindexes si ON so.id = si.id WHERE ic.DATA_TYPE IN('text', 'ntext', 'image') OR CHARACTER_MAXIMUM_LENGTH = '-1')
The article show a simple way we managed to schedule index rebuild and reorg for an SQL instance with 106 databases used by one application using a Scheduled job.
2015-02-17
6,343 reads
Reports stats on what the query optimizer records in the DMVs as missing indexes and what it says the cost savings will be if they were present. Can limit by table / schema name patterns.
2013-01-07 (first published: 2008-08-01)
20,235 reads
Gets single-column and cumulative-column selectivity stats and @Top largest dupe sets for each cumulative column stepping for a set of one or more columns for a table.
2009-05-03 (first published: 2008-08-01)
2,098 reads
Searches database-wide for multiple indexes in the same table which has the same column as the first seek key. Optionally limits to LIKE wildcard patterns of table and schema names.
2008-10-17 (first published: 2008-08-01)
3,103 reads
Reports index stats, index size+rows, member seek + include columns as two comma separated output columns, and index usage stats for one or more tables and/or schemas.
2012-12-27 (first published: 2008-08-01)
7,756 reads