SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by William Talada / Find botched constraints, idxs, fks / Latest Posts

RE: Find botched constraints, idxs, fks

Douglas Osborne-456728 — Thu, 05 Aug 2010 12:09:45 GMT

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 ;-)DougCREATE PROCEDURE [dbo].[ExamineAllTables]ASBEGIN 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 ENDEND

RE: Find botched constraints, idxs, fks

henrik staun poulsen — Thu, 05 Aug 2010 01:29:08 GMT

I'm looking for an explanation for some of the abreviations.pk is Primary Keyak is Unique Constraints ??udx is Unique Indexidx is non unique index"+" means that the field is an included field in the indexThe numbers: x.ywhere x means the "index number" (what is this?)and y means the Index Column IDThe sproc does a good job in showing the relationship between indexing and FK. Thank you.Best regards,Henrik Staun Poulsenwww.stovi.com

RE: Find botched constraints, idxs, fks

Nadrek — Tue, 29 Dec 2009 10:30:06 GMT

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

Find botched constraints, idxs, fks

Bill Talada — Fri, 11 Dec 2009 23:29:14 GMT

Comments posted to this topic are about the item [B]Find botched constraints, idxs, fks[/B]