Set all columns to NULLable - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Set all columns to NULLable

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-11 : 06:02:53

Does anyone have a script to hand for setting the NULL property of all columns in a DB to TRUE?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-11 : 06:06:22

there's a field called isnullable in syscolumns table.
that information is stored there.
how wise it is to directly update that column is another question...

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-11 : 07:51:26

It is entirely UNWISE, do not do it. This script should do the trick:

SELECT 'ALTER TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME) + ' ALTER COLUMN ' +
quotename(COLUMN_NAME) + ' ' + DATA_TYPE +
CASE WHEN DATA_TYPE LIKE '%char' OR DATA_TYPE LIKE '%binary'
THEN '(' + cast(CHARACTER_OCTET_LENGTH as varchar) + ') ' ELSE ' ' END + 'Null'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='myTable' AND Is_Nullable='No'

Copy the code it generates and run it in a new query window.

Remember that you cannot alter a column that has a default, constraint, or index on it with dropping them first, and you can't make a primary key column nullable.

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-11 : 10:04:51

And why would you want to do this in the first place...

I'm all for NULLS, but not where it doesn't make sense...

Brett

8-)

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-11 : 11:02:48

easy on the criticism guys!
It wont stay like that!
I've got another script that will undo this for most columns
But this is for a target db in a dts operation. If I do it this way it will be easier for me to populate the target db in stages.
Do I have your approval?
Good looking script
I'll give it a try
thanks so much

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-11 : 11:57:16

chill....

Why not create a series of staging tables in the first place...then when everything is audited after your dts process (in stages?) then load the final tables.

Got lots of logic in the packages don't ya....

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-11 : 12:00:46

EDIT: Oh, and it's not criticism to tell you not to mess with the system tables...it's good advise...

Which of course, you don't have to take...

Unless you're referring to my question as to why?

Never mind the fact that ALTER in SQL Server is a bit funky...

Now where did I put that article Nigel wrote....

Good Luck

Brett

8-)

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-11 : 13:42:57

"Why not create a series of staging tables in the first place...then when everything is audited after your dts process (in stages?) then load the final tables."
I've heard a few people talk about "staging tables". I dont quite get that. How is that better?

"Got lots of logic in the packages don't ya...."
yes... what's wrong with that. Thats what its there for, not so?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-11 : 13:45:20

EDIT:
"EDIT: Oh, and it's not criticism to tell you not to mess with the system tables...it's good advise..."
I'm not messing with the system tables though am I. I just want to set nullable on the tables I created.

"Which of course, you don't have to take..."
Trust me, I'm humble. If what you say makes sense, i will happily listen!!!

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-11 : 14:29:16

To me Staging tables are tables that I use that are similar to the destination table.

Usually I have all the column datatype defined as varchar.

This allows me to bcp the data in to in a sproc, use t-sql to audit the data and show me any problems..invalid dates, numbers, dup keys, invalid constraints, what have you.

Once the data is verified, I then insert it in to the destination table..or sometimes I just bcp the original file to the destination to save on logging.

If I have known anomalous data problems I would fix it with t-sql and either insert it, or bcp out, then bcp to destination.

I don't like to use DTS...and I find t-sql very flexible

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2005-03-12 : 00:51:48

If you just want an EMPTY table to put data in to mess around with (a.k.a. a stagin table) why not script the existing table(s) and change the NOT NULL to NULL etc.

What about the PK columns? Still want them to prevent duplicates? They need to be NOT NULL ...

I think Robs example will miss DECIMAL/NUMERIC types, and will try to set PK columns to NULL ... all of which can be sorted, if that 's the way you want to go.

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-12 : 07:38:39

quote:
I think Robs example will miss DECIMAL/NUMERIC types

Crap. It'll miss the precision and scale settings. This should fix that:

SELECT 'ALTER TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME) + ' ALTER COLUMN ' +
quotename(COLUMN_NAME) + ' ' + DATA_TYPE +
CASE WHEN DATA_TYPE LIKE '%char' OR DATA_TYPE LIKE '%binary'
THEN '(' + cast(CHARACTER_OCTET_LENGTH as varchar) + ') '
WHEN DATA_TYPE IN ('numeric','decimal') THEN '(' + cast(NUMERIC_PRECISION as varchar) + ',' + cast(NUMERIC_SCALE as varchar) + ') '
ELSE ' ' END + 'Null'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='myTable' AND Is_Nullable='No'

Thanks Kristen.

Kristen
Test

22859 Posts

Posted - 2005-03-13 : 08:08:30

"Thanks Kristen"

Just ignoring the PK columns to go then, I reckon ...

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-13 : 08:34:30

No, I mentioned them earlier, I just don't feel like adding the JOINs. Besides, I'm of the opinion that a good database user should KNOW the primary key column(s) of all their tables.

And they'll get a nice fat error if they try to alter the column anyway.

Kristen
Test

22859 Posts

Posted - 2005-03-14 : 01:56:01

Ah, OK. Just add

COLUMN_NAME <> 'ID'

to the where clause then

Kristen

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-14 : 03:32:47

thanks guys
especially robvolk for the revision on that SELECT statement
genious

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-14 : 04:28:55

>> COLUMN_NAME <> 'ID'

Pure rocketscience, wasn't that what they did when they reprogrammed the marsprobe hehehe?
Set all columns to null -> transfer data -> set all columns to not null -> crash...

>> I've got another script that will undo this for most columns
For most columns...

I will come back to this thread whenever I have a question about database design and DTS

How about this: EXEC('<robs statement>'), that would fit nicely into a DTS execute SQL step...
genious

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-03-14 : 08:38:08

Are you creating a SQL Virus Rockmoose?!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-14 : 08:45:53

quote:
Originally posted by Kristen

Are you creating a SQL Virus Rockmoose?!

ROTFL
You are genius Kristen!

PS.
I don't have to create a NULL virus, somebody else already did

PS2.
But as they say, one can benefit from living in symbiosis with some parasites...

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-03-14 : 14:28:18

Great thing about writing viruses : No after sales support!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources