Find botched constraints, idxs, fks

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by William Talada » Find botched constraints, idxs, fks

Find botched constraints, idxs, fks

Rate Topic

Display Mode

Topic Options

Author

Message

Bill Talada

Posted Friday, December 11, 2009 11:29 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:31 AM
Points: 65, Visits: 443

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

Post #833291

Nadrek

Posted Tuesday, December 29, 2009 10:30 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:28 PM
Points: 675, Visits: 2,031

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

Post #839932

henrik staun poulsen

Posted Thursday, August 05, 2010 1:29 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:39 PM
Points: 1,133, Visits: 856

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
www.stovi.com

Post #964030

Douglas Osborne-456728

Posted Thursday, August 05, 2010 12:09 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327

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 Wink

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

Post #964583

« Prev Topic | Next Topic »

Permissions