Updating Defaults in Multiple Tables

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

Updating Defaults in Multiple Tables

Author

Topic

HeartOfDorkness
Starting Member

3 Posts

Posted - 2009-04-24 : 13:54:04

Hi folks,

I'm a total SQLServer newbie, so please forgive any resulting stupidity.

First and foremost, this is not at all urgent. I can (and probably will) manage this task "the long way"; I'm just trying to learn better techniques for the future.

I'm working in SQL Server 2000. I've created a bunch of tables, each of which includes a particular required field. The fieldname is composed of the table name plus a suffix. So, TABLEA has a field called tableAField, TABLEB has tableBField, and so on. This field name pattern is unique within each table. And no, these are not the real names. None of these tables is populated yet.

Now, I've been told to assign a default value to every one of these fields - that is, the same value for this particular field in every table. There aren't thousands of tables, so right now, I'm just going into each table layout one by one and entering the default. And that's fine. But it seems to me that I should be able to do this in one fell swoop, or at least, very few swoops.

Is it possible to set up a query that goes through each user-defined table (or a specifiable set of tables), finds the appropriate field (using COLUMN_NAME like '%Field' or some such), and then sets the default to the specified value? If so, would it use a cursor? Loop? sp? Magical elves?

I'm not necessarily looking for code - if there is something relevant in BOL or a forum thread or article, I would greatly appreciate it if you could just point me in the right direction.

Thanks for your help!
Dorkness

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-24 : 14:02:22

Check out the system view: INFORMATION_SCHEMA.Columns

That will let you search by column name and get the table name the column belongs to and wether or not it has a default value.

Then you can use that data with a cursor or loop to update all the needed columns.

HeartOfDorkness
Starting Member

3 Posts

Posted - 2009-04-24 : 14:59:25

Thanks!

Unfortunately, I'm still missing something. I was able to search by column name and so forth, but I couldn't perform the update. I tried the following (I may not actually need a loop):

update INFORMATION_SCHEMA.Columns
set COLUMN_DEFAULT = [my default value]
where TABLE_NAME like '[table_name_prefix]%'
and COLUMN_NAME like '%[field_name_suffix]'

...and I get:
"Server: Msg 4406, Level 16, State 2, Line 3
Update or insert of view or function 'INFORMATION_SCHEMA.Columns' failed because it contains a derived or constant field."

I wasn't sure whether this was saying the field I'm trying to update (that is, column_default) is derived/constant, or just that there is some such field in that view, so I created my own view based on the relevant select of INFORMATION_SCHEMA.Columns with just one column, column_default. I then tried the update again:

update [my view]
set COLUMN_DEFAULT = [my default value]

...and I get bupkis. Same error.

Can I not update COLUMN_DEFAULT en masse? Is it considered a constant/derived value? Or perhaps I can't update through INFORMATION_SCHEMA.Columns OR any views based on it?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-24 : 15:14:07

You can't define column defaults in that fashion, system tables and views are not updateable in SQL Server.

You can however use the INFORMATION_SCHEMA data to generate the necessary SQL:

SELECT 'ALTER TABLE ' + quotename(table_name) + 
' ADD CONSTRAINT [DF_' + table_name + '_' + column_name + 
'] DEFAULT ''myDefaultValue'' FOR ' + column_name
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME like '[table_name_prefix]%'
and COLUMN_NAME like '%[field_name_suffix]'

Copy those results and paste them into a new query window and run the code. You need to change the 'myDefaultValue' section to the literal default you want for that column(s).

HeartOfDorkness
Starting Member

3 Posts

Posted - 2009-04-24 : 15:36:02

That, my friend, is a thing of beauty.

Many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources