asked on

Changing ALL Columns from Allow Nulls

I have created 35 tables previousily with allow NULL on each column.

Is there a TSQL code that will allow me to programatically change all columns from NULL to NOT NULL. The tables do not have the same structure and manually altering each is tedious and time consuming.

Help

Jim P.

You may want to use something like the Database Publishing Wizard (link below) to script out your database and then do a search and replace in the script.

Now the question is: Why?

Some implications in this kind of change include that you would have to have a date in all date fields and you have to run updates on all fields to set a default value for every field.

Microsoft SQL Server Database Publishing Wizard 1.1
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Jim P.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JenebyM

ASKER

Why,

Some of our queries with multiple joins are getting more complicated with Nulls present in various tables. We want to ensure that the table columns have default values instead of NULL and that these are meaningful to data returned for reporting particularly in multiple joins.

I will try the scripting and get back to you!

thanks

Jim P.

That sounds more like an issue of normalization and Primary/Foreign Keys than changing every field in every table.

But every table should have a primary key without nulls in it.

JenebyM

ASKER

Part of the problem is creating PKs (2 fieilds, unique) and Index currently on null fields. First step is to remove Null fields. I have gone down the track of doing this manually and re-creating the correct PKs/FKs.

The remaining tables are smaller and our query results much better without the nulls.

Thanks

DBAduck - Ben Miller

You can script this using SMO, but you would need either Powershell on your machine and SMO (which is installed when you install Management Studio) or some way of running .NET code. Are you putting defaults in place with the columns that allow nulls? Are all columns allowing NULLs or just some?

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JenebyM

ASKER

Opted to make these changes manually and this is now completed. Points awarded for otherwise good ideas that I trialled successfully with sample tables.

Jim P.

Gladd to be of assistance.