Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find botched constraints, idxs, fks Expand / Collapse
Author
Message
Posted Friday, December 11, 2009 11:29 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:15 PM
Points: 132, Visits: 846
Comments posted to this topic are about the item Find botched constraints, idxs, fks
Post #833291
Posted Tuesday, December 29, 2009 10:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 865, Visits: 2,379
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
Posted Thursday, August 5, 2010 1:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:07 AM
Points: 1,364, Visits: 989
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
Posted Thursday, August 5, 2010 12:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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

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 »

Add to briefcase

Permissions Expand / Collapse