Column Information

Here's another extremely useful script that lists column information for all tables in a given database. The attached view contains just about anything related to a column definition in SQL Server 2000. The query that you'll see after the view definition contains the following columns.

• Table Name
• Column Name
• Type (and size if applicable)
• Nullability
• Default Value
• Description

There is a lot more information that you can gleam from the view, but the above are the basics that I find really handy.

You can download it here...

Extended Properties of Tables

Here's another old but handy script that will list out extended properties on all tables for a given database. This comes in handy for documentation purposes, or to simply learn more about your db if your tables have any extended properties.

You can download it here...

Foreign Key Information

Quite some time back I put together a script for SQL Server 2000 that lists out all of the primary key to foreign key relationships. It's a simple script that generates a nice little listing which can be used to get a better understanding of a new database, or simply an easy way to help document an existing one.

You can download it here...

Case sensitivity in SQL Server

Microsoft SQL Server by default is case-insensitive which for the most part is very handy when searching and sorting data. There are times though where this isn't ideal, such as managing usernames or passwords.

If you take a look at the DDL of a table, you'll notice that most of your text-based columns including char and varchar fields use "COLLATE SQL_Latin1_General_CP1_CI_AS". If you would like certain columns to be case sensitive you will want to use "COLLATE SQL_Latin1_General_CP1_CS_AS" for your column definition. Please note the substitution of CI for CS in the collation statement.

If you don't have the option to change your column definition, there are a few other options to consider such as casting your columns to varbinary values when performing searches.

Migrating Legacy Applications

My most recent project involves WebFocus from Information Builders. Although WebFocus is a very powerful platform, the client was still using a fairly old version so we opted to move the application to SQL Server. This in itself was quite a task as WebFocus doesn't offer an ODBC connector to pull data out of it's proprietary database.

WebFocus allows you to export data directly to SQL Server, Excel, CSV, and many other file formats or database systems, but migrating a real world application out of WebFocus wasn't nearly as simple as they lead you to believe.

In comes Data Integrator, a product from Pervasive. Data Integrator is a very powerful tool that allows you to move data to-and-from roughly 150 different file formats and database platforms. Data Integrator allows you to build an easy and reproducible process to migrate data out of your legacy applications.

This white paper about Intuit shows the true power available within Pervasive's suite of products, and I highly recommend taking a look if you find yourself needing to migrate a legacy application. Although the white paper shows an extraordinary use of their products, they can be used to simplify even the most basic of integration projects.

Feel free to ping me at michael@omnicypher.com if you find yourself heading down this road. I would be more than happy to help out and/or share some of my experiences with this process.

SQL Server 2000 on Vista?

I read a number of articles that mentioned SQL Server 2000 will not install and/or is not supported on Vista. I just wanted to let you know that it WILL install. The only major caveat that I ran into was in enterprise manager when trying to connect to the 'local' instance. I had to dump that connection and connect to the actual named install of the sql server (which was my server name).

There is also an issue with SQL Server starting properly when Vista loads up. The MSSQLSERVER service was set to start up "Automatic" but in fact this doesn't work. If you change the startup type to "Automatic (Delayed Start)" things will work as expected.

All of that aside, based on the articles I read, along with all of the prompts Vista throws at you to essentially not install it, I don't know if I would install it on a production system, as Microsoft probably won't support it.

Good luck!