SqlDbx Forum

SqlDbx

You are not logged in.

#1 2010-03-24 06:13:34

ptoniolo
Member

Tools/Generate Scripts

I used Tools/Generate Scripts to get the structure of a sql2008 database of a customer. I use the 3.43 version of sqldbx.

There were some problems, that I had to fix manually. Here are the issues:

1) you correctly quote (with square brackets) object names (column/table/index names) containing spaces or strange characters, like "°". But you don't quote names with dashes inside. And you should do it!

2) you do not quote object names that are keywords, like [TO] or [User]. I know it's stupid to use names like that, but many customers have this bad habit. Consider that, when in doubt, I quote everything: maybe you could add a checkbox to allow this!

3) you assume that the primary key constraint is always clustered. In sqlserver, only one of the indexes can be clustered (it's like the IOT in oracle). If the clustered index is not the primary key, the code produced is something like:
    create table <tabname>
        (
        <columndefinitions...>,
        constraint <pkname> primary key (<pkcols>)
        )
    go
    create clustered index <idxname>
        on <tabname> (<idxcols>)
    go
Well... the create index fails, because sql assumes that the pk creation is clustered, and you can't have two clustered indexes on the same table. I think you should use the (non)clustered flag when you build the pk constraint string, and when the pk is nonclustered, use a syntax like this in the create table statement:
    constraint <pkname> primary key nonclustered (<pkcols>)

Thank you
Pietro

Offline

#2 2010-03-24 10:55:52

sqldbxhelp
Administrator

Re: Tools/Generate Scripts

1. Will be fixed.
2. Probably can be an option.
3. Will have to investigate.

Offline

#3 2011-03-07 13:49:10

ptoniolo
Member

Re: Tools/Generate Scripts

I can see today that my 3rd suggestion has been included in the newer versions of sqldbx, the export process now correctly identifies a non-clustered primary key with the keyword "nonclustered".

Thank you!
Pietro

Offline

Board footer

Powered by FluxBB