I guess you didn't read what everyone has written.

Doug

Rich,

Nice add - I didn't know that NOT FOR REPLICATION would do that.

This has been a really good article and an extensive followup discussion.

Best,

Doug

Rich,

Why would you just randomly run that against ALL tables? Mine was targeted to the FKs marked as untrusted.

Wondering,

Doug

Tony,

Your old code you reference does not work - http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31760/ - Invalid column name 'SchemaName' - you need to add that to the data.Dictionary definition.

Best,

Doug

All,

I came up with this little trick - run the query

SELECT Name AS [Constraint], object_name(parent_object_id) AS [Table]

FROM sys.foreign_keys

WHERE Is_Not_Trusted = 1

then run this

SELECT 'ALTER TABLE ' + OBJECT_NAME( Parent_Object_ID )...

Rudy,

Nice utility script.

Doug

Nice Add Jean!

Awesome - now these are the kind of articles we need.

Doug

Michael,

I get

Warning! The maximum key length is 900 bytes. The index 'PK_AliasRules' has maximum length of 1025 bytes. For some combination of large values, the insert/update operation will fail.

for...

What is this script supposed to demonstrate? This seems like a silly post when there is no discussion as to what it accomplishes and why you would use this 'technique.'

My...

Joe,

I didn't know that technique - here is your code with the correct columns demonstrating the efficiency.

-- List all entries regardless of frequency

SELECT *, 1 AS grp_size FROM Emp_Details

INTERSECT

SELECT emp_name,...

Paul,

I knew you were in rarefied air when a Google search on 'NormalizeGbAgg SQL' returned 36 results and Bing returned 2 - both yours!

10 Star article

Doug

Very nice followup - good add!

Doug

I tweaked your code a bit:

CREATE PROCEDURE [dbo].[IndexLister]

AS

BEGIN

SET NOCOUNT ON

DECLARE

@idxTableName SYSNAME,

@idxTableID INT,

@idxname SYSNAME,

@idxid INT,

@colCount INT,

@IxColumn SYSNAME,

@IxFirstColumn BIT,

@ColumnIDInTable INT,

@ColumnIDInIndex INT,

@IsIncludedColumn INT,

@sIncludeCols VARCHAR(4000),

@sIndexCols VARCHAR(4000),

@sSQL VARCHAR(4000),

@sParamSQL VARCHAR(4000),

@location SYSNAME,

@IndexCountINT,

@CurrentIndexINT,

@CurrentColINT,

@NameVARCHAR(128),

@IsPrimaryKeyTINYINT,

@FillfactorINT

CREATE TABLE #IndexListing

(

[IndexListingID]INT IDENTITY(1,1) PRIMARY...

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