Find botched constraints, idxs, fks

  • Comments posted to this topic are about the item Find botched constraints, idxs, fks

  • An interesting query.

    I would note that if one is looking for botched foreign keys, one must look for both disabled and enabled but untrusted foreign keys and note them.

    I'd also have to modify it to run from a single database against columns on another database (with EXEC 'USE <db>; <statement>' if need be).

  • I'm looking for an explanation for some of the abreviations.

    pk is Primary Key

    ak is Unique Constraints ??

    udx is Unique Index

    idx is non unique index

    "+" means that the field is an included field in the index

    The numbers:

    x.y

    where x means the "index number" (what is this?)

    and y means the Index Column ID

    The sproc does a good job in showing the relationship between indexing and FK.

    Thank you.

    Best regards,

    Henrik Staun Poulsen

    http://www.stovi.com

  • Here's a quick script to run it against all of your tables. I've still got to figure out your syntax a bit better 😉

    Doug

    CREATE PROCEDURE [dbo].[ExamineAllTables]

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE

    @TableCntINT,

    @CurrentTableINT,

    @TableSYSNAME

    CREATE TABLE #TableListing

    (

    [IndexListingID]INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    [TableName]SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    )

    INSERT INTO #TableListing( [TableName] )

    SELECT [Name]

    FROM sys.tables

    ORDER BY [Name]

    SELECT @TableCnt = @@ROWCOUNT, @CurrentTable = 1

    WHILE @CurRentTable <= @TableCnt

    BEGIN

    SELECT @Table = TableName

    FROM #TableListing

    WHERE [IndexListingID] = @CurrentTable

    EXECUTE dbo.ShowColumnUsage @Table

    SET @CurrentTable = @CurrentTable + 1

    END

    END

  • Thanks for the script.

  • henrik staun poulsen (8/5/2010)


    I'm looking for an explanation for some of the abreviations.

    pk is Primary Key

    ak is Unique Constraints ??

    udx is Unique Index

    idx is non unique index

    "+" means that the field is an included field in the index

    The numbers:

    x.y

    where x means the "index number" (what is this?)

    and y means the Index Column ID

    The sproc does a good job in showing the relationship between indexing and FK.

    Thank you.

    Best regards,

    Henrik Staun Poulsen

    http://www.stovi.com[/quote%5D

    PK = Primary Key

    AK = Alternate Key

    UDX = Unique Index

    IDX = Non-Unique Index

    + = Included column

    x.y = Index#.Column#

    I still use this before each release as a sanity check to spot redundant or missing indexes.

  • thank you very much

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply