This script lists all the foreign keys ,the referenced and the referencing tables , along with the column information.
CREATE VIEW dbo.VwuConstraintAndTableName AS --------------------------------------------------- - - - BY Vishy - - -------------------------------------------------- select top 100 percent o.name as ForeignKeyName, object_name(o.parent_obj) as ChildTable, c.name as ReferencingColumnName,object_name(rkeyid) as ParentTable, pc.name as ReferencedColumnName from dbo.sysforeignkeys join sysobjects o on constid = o.id join syscolumns c on c.id = o.parent_obj and c.colid = fkey join syscolumns pc on pc.id = rkeyid and pc.colid = rkey order by ChildTable
This article has a description of an index utilization tool that Ron Johnson uses to analyze the effectiveness and use of indexes in a system.
2012-07-04 (first published: 2011-07-05)
19,266 reads
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,787 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
635 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 […]
2010-04-19 (first published: 2003-03-19)
2,211 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,748 reads
