WHERE OR SCRIPT - 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

WHERE OR SCRIPT

Author

Topic

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-24 : 05:33:22

Dear Friends,

I've missed something, Im guessing I have to nest for or within Where but cant see it

Please help

Heres my attempt

use RIQDB1
IF EXISTS (SELECT DISTINCT 'Alter table '+ table_name +
' ADD CONSTRAINT DF_'+ table_name + '_' + column_name +
'DEFAULT ' + '’’ FOR ' column_name FROM Information_schema.columns
WHERE column_name ='%Title%'
OR column_name ='%Description%'
Go

Cm

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-24 : 05:36:06

What do you want to do?

Madhivanan

Failing to plan is Planning to fail

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-24 : 05:39:14

Hi Madhivanan,

Thankyou very much for your help

Script intentions
1. Generate script that will add a default to any column in any table that has title or description in the column_name

Cm

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-24 : 05:47:30

What is the default value do you want to set?

Run this in QA

SELECT DISTINCT 'Alter table '+ table_name +
' ADD CONSTRAINT DF_'+ table_name + '_' + column_name +
' DEFAULT ' + ''''' FOR '+ column_name FROM Information_schema.columns
WHERE column_name ='no'
OR column_name ='id'

Copy the result back to QA and run them one by one

Madhivanan

Failing to plan is Planning to fail

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-24 : 06:02:20

Dear Madahavan,

This worked with like statements. Thankyou Very Much

USE RIQDB1
SELECT DISTINCT 'Alter table '+ table_name +
' ADD CONSTRAINT DF_'+ table_name + '_' + column_name +
' DEFAULT ' + ''''' FOR '+ column_name FROM Information_schema.columns
WHERE column_name Like '%Title'
OR column_name Like '%Description'

Cm

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-24 : 10:45:58

>>Dear Madahavan,

Spell the name correctly

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources