Find botched constraints, idxs, fks

  • Bill Talada

    SSChampion

    Points: 11956

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

  • Nadrek

    SSC-Insane

    Points: 20039

    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).

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6339

    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

  • Douglas Osborne-456728

    SSCommitted

    Points: 1569

    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

    @TableCnt INT,

    @CurrentTable INT,

    @Table SYSNAME

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • Bill Talada

    SSChampion

    Points: 11956

    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.

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6339

    thank you very much

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

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