Design Oversight - Preliminary Review

  • K. Brian Kelley

    SSC Guru

    Points: 114486

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/designoversight-preliminaryreview.asp

    K. Brian Kelley
    @kbriankelley

  • ckempste

    SSCoach

    Points: 17983

    Brian

    Great article Brian, only things worth adding:

    a) FK columns that are not indexed

    b) Cluster indexes on identity columns

    c) No collected or stale statstics

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • ckempste

    SSCoach

    Points: 17983

    Brian

    Here is another script to list all columns that have a "char" datatype where they possibly should be "varchar".

    select sysobjects.name, sysusers.name, syscolumns.name

    from syscolumns, sysobjects, sysusers

    where syscolumns.type = 47

    and syscolumns.id = sysobjects.id

    and sysobjects.uid = sysusers.uid

    and sysobjects.xtype = 'U'

    order by 2,1


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • K. Brian Kelley

    SSC Guru

    Points: 114486

    Sounds good, Chris, I like the suggestions and the script. Looks like I need to write a part II. 🙂

    K. Brian Kelley

    bk@warpdrivedesign.org

    K. Brian Kelley
    @kbriankelley

  • ckempste

    SSCoach

    Points: 17983

    Brian

    Here is another classic problem... in a recent system consolidation I ended up with 6 or so tables that shared the same name but in different case. Perhaps a more advanced script would be to compare columns (maybe later!).

    -- List possible duplicate tables based on table-name

    select a.name

    from sysobjects a, sysobjects b

    where upper(a.name) = upper(b.name)

    and a.id <> b.id

    and a.xtype = 'U'

    and b.xtype = 'U'


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • kfbsluggo5

    Newbie

    Points: 5

    I tried the query and got all tables.....with or without indexes and primary keys?????

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720952

    Good job Brian. A great topic and definitely place for a Part II!!!

    Steve Jones

    steve@dkranch.net

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply