SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clustered Index Analyser


Clustered Index Analyser

Author
Message
jaberjung-531432
jaberjung-531432
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 256
Thanks for sharing this script. i just tried it on 2008, working fine. But getting errors while running this script on 2000.
Greg Milner
Greg Milner
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 494
Wonderful script, Cameron. Thanks!

I have to come up with an Auditing solution for data changes and you script helped me happily find out that most of my tables have only one column for the clustered index which is almost always the primary key. This will make my Auditing solution easier.

Very useful, thank you so much.



G. Milner
EdSwiedler
EdSwiedler
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 94
Everything worked well until the script encountered a partitioned table.
Dewes van Lohuizen
Dewes van Lohuizen
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 472
Hello Cameron,
I am always trying to learn something.
I ran your script and got:
Msg 468, Level 16, State 9, Line 152
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I do not know enough about the scripts to make it running but possibly you can handle that.

Thanks,
Dewes.

Dewes van Lohuizen,
DBA at CSC Netherlands
Private interest: www.mikondo.nl
cameron.wells
cameron.wells
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 185
Hi Dewes,

I'll try and explain this in layman's terms, as these are the terms I understand.

This issue is not uncommon I believe. Collation's describe the ordering of data within columns. Mainly collation's are used to separate different languages alphabet ordering, as these differ between languages.

Specifically for your issue, Some columns on the database you are running the script on are using collation Latin1_General_CI_AS, whereas other columns are using SQL_Latin1_General_CP1_CI_AS.

- SQL_Latin1_General_CP1_CI_AS (legacy SQL Specific collation, which doesn't support unicode, and and will be removed from SQL Server one day)
- Latin1_General_CI_AS (windows collation which supports unicode)

Run this script to identify the columns on your database and their different collations:

SELECT
C.Name,
C.Collation_Name
FROM SYS.COLUMNS C

JOIN SYS.OBJECTS O
ON O.Object_id = C.Object_id

WHERE C.OBJECT_ID > 100
--AND C.Collation_Name <> 'Latin1_General_CI_AS'
AND O.Type = 'U'

I believe that because these collations aren't consistent, the error is occurring when running my script. Other scripts may also have a similar issue with the inconsistent collations.

You can overcome this by casting collations at every join, but i wont be updating my script to account for this as it will be time consuming. I'd advise that your column collations be consistent.

Regards,

Cameron
Dewes van Lohuizen
Dewes van Lohuizen
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 472
Hi Cameron,
thanks for your explanation.

Dewes van Lohuizen,
DBA at CSC Netherlands
Private interest: www.mikondo.nl
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search