alter tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

alter tables

Author

Topic

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-20 : 22:31:28

Dear friends

I would like to alter columns so that I have have empty string records instead of null. How do I query all tables and all columns so I can alter the column properties programmatically?

Thanks

Cm

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-20 : 22:39:20

you can query through information_schema.columns,
you can retrieve the information for each column on which table and what default value it has

then script out the command or use dsql...

--------------------
keeping it simple...

gongxia649
So Suave

344 Posts

Posted - 2006-04-20 : 22:50:51

no im not looking for that

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-20 : 22:54:53

HUH?

you mis-posted again gongxia649

quote:
Originally posted by gongxia649

no im not looking for that

--------------------
keeping it simple...

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-20 : 23:25:35

Thanx Jen

I get a full listing in results but dont know how to "script out"

This is so frustrating.....

Cm

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-20 : 23:36:59

"I would like to alter columns so that I have have empty string records instead of null."
you mean update the value to empty string instead of null ?


update    yourtable
    set   yourcolumn = ''
    where yourcolumn is null

"How do I query all tables and all columns so I can alter the column properties programmatically?"
What properties do you want to change ?

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-20 : 23:45:24

Thankyou so much guys

Yes, I want to correct any columns that have null and have empty strings in them as opposed to not null.

I also found how to access names of tables
select name from sys.tables

Cm

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-20 : 23:49:49

do this...
save the script generated from this, you will need to run this after you've updated the columns not to accept null...

select 'alter table ' + table_name
+ ' add constraint ' + @constraintname + ' default '''' ' from information_schema.columns
where column_default is null

update all the null values to ''

update table set columname=''
where columnane is null

then...

issue this and run the relative script (you may need to filter)

select 'alter table ' + table_name
+ ' alter column ' + column_name + ' ' + data_type + '(' + character_maximum_length + ') not null'
from information_schema.columns
where column_default is null

then run and issue the first scripts you generated

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-20 : 23:57:21

"select name from sys.tables"
Are you using MS SQL Server ?

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-21 : 00:01:13

I can get the column names as text but on one line

how do I get them to list?

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-21 : 00:04:52

I am using ms sql server 2005 de

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-21 : 00:06:19

oh... be sure to make a backup first before making any changes

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-21 : 00:07:20

quote:
Originally posted by Chopsmum

I am using ms sql server 2005 de

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-21 : 00:09:24

"Yes, I want to correct any columns that have null and have empty strings in them as opposed to not null."
Sorry. just want to be sure what exactly you want.
1. You want to change the column's default value as in jen's script ?
2. You want to update the column's value to empty string '' where the value is null ?

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-21 : 00:22:58

Khtan,

I have only just started using sql as you can tell. I usually catch on quick so this is frustrating.

I have read somewhere that nulls should be avoided whenever possible. I am creating a web app. (SLOWLY!!!) so nulls will cause havoc in every transaction if I am not careful.

So Where I have varchar datatypes, I want to allow empty strings rather than permit null values.

The problem is that I have 88 tables and nearly 700 columns and am trying to do it in one step.

eg. where a column in any table is varchar, I want to allow empty strings rather than just allow nulls.

Backup sounds like a great idea!!! Thanks Jen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-21 : 00:44:37

"So Where I have varchar datatypes, I want to allow empty strings rather than permit null values."
Actually it is perfectly OK to have NULL in column with varchar datatype.

"I am creating a web app. (SLOWLY!!!) so nulls will cause havoc in every transaction if I am not careful."
I can help you with setting column value to empty string but that is not the source of your problem. And looks like your actualy problem is over at the web app, your front end. Is it ASP.NET ?

I am not familiar ASP.NET maybe you can post your ASP problem over at the ASP.NET forum

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-21 : 01:19:40

you should have some sort of validation in your apps side

anyways, the default value will be saved with '' if ever you don't specify any value when saving a record

have fun

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-21 : 01:54:22

If Nulls make the problem, why cant you handle that in front end application?
or use Insull(col,'') when selecting data from table

Madhivanan

Failing to plan is Planning to fail

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-21 : 03:09:12

Im in the design phase and trying to eliminate possible errors whilst I still can

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-21 : 17:04:37

Yes don't use nulls. They are a possible source of errors.
Furthermore you will not need to have isnull / coalesce logic sprinkled in your code.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources