Database Shrink Problem

I have a database which was 120 MB before changed the data
types on some columns in some tables. I have changed
the 'char' data types to 'varchar' data type on many
columns expecting the database size to get smaller.
Instead, it went up from 120 MB to 131 MB (This is the
pure data part not the space allocation part).
Did I understand the data types wrong '
Thanks for any help..No - you have probably understood the types correctly, but changing from =char to varchar will have 2 effects:
1 An extra 2 bytes per varchar column will be allocated for the length =data of each column, thus the row will potentially increase in size and =may cause extra pages to be allocated as a result.
2. The char colukns are padded with spaces so to get rid of those you =will ned to do Update x set col=3Drtrim(col) on each column you have =changed.
Fnally having done 1 and 2 drop and recreate the clustered index (if =any) to force the rows to be re-allocate dto new pages with theire =reduced (hopefully) sizes. If no CI exists then create one and drop it =again to have the same effect.
Mike John
"calvin" <anonymous@.discussions.microsoft.com> wrote in message =news:008e01c3c96d$bc5a7950$a101280a@.phx.gbl...
> I have a database which was 120 MB before changed the data > types on some columns in some tables. I have changed > the 'char' data types to 'varchar' data type on many > columns expecting the database size to get smaller. > Instead, it went up from 120 MB to 131 MB (This is the > pure data part not the space allocation part).
> > Did I understand the data types wrong '
> > Thanks for any help..
> >|||Thanks Mike......
>--Original Message--
>No - you have probably understood the types correctly,
but changing from char to varchar will have 2 effects:
>1 An extra 2 bytes per varchar column will be allocated
for the length data of each column, thus the row will
potentially increase in size and may cause extra pages to
be allocated as a result.
>2. The char colukns are padded with spaces so to get rid
of those you will ned to do Update x set col=rtrim(col) on
each column you have changed.
>Fnally having done 1 and 2 drop and recreate the
clustered index (if any) to force the rows to be re-
allocate dto new pages with theire reduced (hopefully)
sizes. If no CI exists then create one and drop it again
to have the same effect.
>Mike John
>"calvin" <anonymous@.discussions.microsoft.com> wrote in
message news:008e01c3c96d$bc5a7950$a101280a@.phx.gbl...
>> I have a database which was 120 MB before changed the
data
>> types on some columns in some tables. I have changed
>> the 'char' data types to 'varchar' data type on many
>> columns expecting the database size to get smaller.
>> Instead, it went up from 120 MB to 131 MB (This is the
>> pure data part not the space allocation part).
>> Did I understand the data types wrong '
>> Thanks for any help..
>>
>.
>

Database Schema Documentation Tool?

I am looking for a database documentation tool/script that I can run against
a particular database to generate a document of all the tables, columns, and
relationships in the database. I have seen a data dictionary that lists the
table names with links to the table details further down the document, and
clicking on the relationships jumps you to that table in the document. That
particular one was generated out of the programming revision control system
they were using into xml and xsl files. I am looking for something that can
generate the information out of the metadata in SQL Server.
Thanks
I think you may be thinking of Enterprise Architect
http://www.sparxsystems.com.au/
Or ER/Studio
http://www.embarcadero.com/products/erstudio/index.html
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||Look at ApexSQL Doc from www.ApexSQL.com
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||I love ApexSQL . See the details from below URL:-
http://www.sql-server-performance.com/apex_sql_doc_spotlight.asp
http://www.apexsql.com/sql_tools_doc.asp
You could try the trial version and use it for a month:-
http://www.apexsql.com/downloads.asp
Thanks
Hari
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OyNRBReFHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Look at ApexSQL Doc from www.ApexSQL.com
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Greg Hess" <keadrix@.hotmail.com> wrote in message
> news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>
|||Another effective but much less costly option if you only want
documentation is
SqlSpec from ElsaSoft
Their website at www.elsasoft.org has a trial version and also samples
of the output
T
|||Greg Hess wrote:
> I am looking for a database documentation tool/script that I can run against
> a particular database to generate a document of all the tables, columns, and
> relationships in the database. I have seen a data dictionary that lists the
> table names with links to the table details further down the document, and
> clicking on the relationships jumps you to that table in the document. That
> particular one was generated out of the programming revision control system
> they were using into xml and xsl files. I am looking for something that can
> generate the information out of the metadata in SQL Server.
> Thanks
You might want to try SchemaToDoc for SQL Server
(http://www.schematodoc.com). It exports to a Word doc metadata info
such as primary keys, field info (types, size, nullable, defaults),
indexes, check constraints, foreign key constraints, triggers, views,
stored procedures, and extended properties. It also lets you annotate
your tables and fields and include those comments in the Word doc. An
Enterprise edition can create a series of linked HTML files in addition
to the Word output.
|||Thank you for all your suggestions. After evaluating them I have decided to
go with SqlSpec from Elsasoft (http://www.elsasoft.org/). It does exactly
what I need it to do for a good price.