December 11, 2009 at 11:29 pm
Comments posted to this topic are about the item Find botched constraints, idxs, fks
December 29, 2009 at 10:30 am
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).
August 5, 2010 at 1:29 am
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
August 5, 2010 at 12:09 pm
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
May 18, 2016 at 1:00 pm
Thanks for the script.
May 19, 2016 at 7:00 am
henrik staun poulsen (8/5/2010)
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.
May 20, 2016 at 3:30 am
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