For each user database rebuild indexes, update statistics and shrink. The script will use the system catalog to produce a list of databases. If you want to excluce a database add the excluded db name to the 'not in' list. You can also adjust the target fillfactor.
2003-05-07
1,919 reads
I see scripts all the time on how to reindex all the tables in a db but they are usually too generic and may cause issues. For instance they typically cause the log file to grow very large and may even fill the hard drive. This is intended to be run off hours when db […]
2003-04-24
702 reads
This script reindexes all tables in all databases. Execute the script with the desired fill factor and it will do the rest. There is an option to exclude databases from the reindex. This makes it easy to reindex all newly created databases. Great for the Development environment when Developers are creating database all the time.
2003-03-23
2,014 reads
SQL 2000 OnlyThis script will create a stored procedure and a table in the master database. Simply run the stored procedure and supply a database name as a parameter. It will do the following:1. Gather/Log DBCC SHOWCONTIG statistics (pre defrag)2. Defragment all user indexes in the database3. Gather/Log DBCC SHOWCONTIG statistics (post defrag)4. DBCC DBREINDEX […]
2004-11-11 (first published: 2003-03-19)
2,267 reads
This script uses sysobjects and sysindexes to ID all tables in a database that are indexed, and/or clustered. It then runs through per table all of the indexes and runs DBREINDEX with their fillfactor. This eliminates having to maintain a list of all indexes and having code fail when it tries to modify indexes that […]
2002-12-19
1,834 reads
When creating indexes on tables, it is always best to choose columns that have a high degree of selectivity, or uniqueness. This SP, ShowUniqueness, gives the user an easy way to determine the "uniqueness" percentage of a given column or combination of columns.
2002-10-23
486 reads
We are constantly having to drop indexesfrom tables while repopulating the datain the table. We then have to rebuild the indexes as they were before dropping them.This requires writing a custom drop and recreate index script for each unique situation. If we tryto write scripts ahead of time as soon as the indexing schemechanges on […]
2002-08-06
996 reads
this script describes the steps neccessary to implement full text indexing in TSQL on your SQL SERVER systemCan be used programatically in tsql code
2002-07-10
757 reads
This script returns the index locking strategy informationfor all user tables in a database. It also generates the required TSQL statement to alter locking strategies if required. These statements require CTRL-C and CTRL-V individually into query analyser for execution. Results are best viewed in text only. This TSQL can be wrapped in a stored procedure […]
2002-07-04
1,420 reads
This script will be helpful in detecting duplicate indexes created ( on the same set of field(s) ). These would have been created by accident, because of their existence the performance will be affected.
2002-04-16
1,861 reads
The first version of my accounting assistant could not find a single purchase of...
Why isn’t AI paying off yet? Drawing on lessons from digital transformation, Joyful Craftsmen...
By Steve Jones
I’m really excited about some growth in Flyway support. I’ve been asking about this...
Comments posted to this topic are about the item Pay for Performance, which is...
Hi I have this slow query that is pulling data from a linked server....
Comments posted to this topic are about the item Too Many Choices
I have a table, dbo.MasterBrands, with this data:
BrandID OfficialName 1 Samsung 2 Apple 3 Microsoft 4 Adidas 5 Coca-ColaI run this query. What is returned for Coca-Cola?
SELECT CHOOSE(BrandID, 'Conglomerate', 'Tech and Design', 'Desktop', 'Apparel') FROM dbo.MasterBrands;See possible answers