Oracle's equivalent query in SQL server 2005

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

Oracle's equivalent query in SQL server 2005

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-06-14 : 02:05:07

below is oracle query ,I need to find it's equivalent in SQL server 2005

select * from ALL_TAB_COLUMNS where column_name = 'DEPT'3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-14 : 02:10:18

[code]SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'DEPT'[/code]

KH
[spoiler]Time is always against us[/spoiler]

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-06-14 : 02:41:37

ok that's good

this statement gave me 50 rows.

so what if i have to delete this column related value from all above 50 table's and heir corrpospoding columns?

DO I have to create a statement to delete data
DELETE
FROM [dbo].[PLANT_SECTION]
WHERE [PLANT_SECTION_PLAN_CHR] IN (@Plan1,@Plan2)

and repeat same for 50 above rows?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-14 : 02:49:26

you will need to use Dynamic SQL (pls read The Curse and Blessings of Dynamic SQL)
or the undocumented sp_MSforeachtable (just google for this)

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-14 : 03:59:28

quote:
Originally posted by qutesanju

below is oracle query ,I need to find it's equivalent in SQL server 2005

select * from ALL_TAB_COLUMNS where column_name = 'DEPT'3

What does 3 maen at 'DEPT'3?
Is it a type error?

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-14 : 04:20:24

If this is a one-time deal you can do like this:

SELECT 'ALTER TABLE [' + table_name + '] DROP COLUMN [' + column_name + ']' FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'DEPT'

Then just copy/paste the result of the query to a new query window.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-06-14 : 06:46:22

Actually i m looking data deletion from all above 50 statements

like for first table i can form the statement like

DELETE
FROM [dbo].[PLANT_SECTION]
WHERE [PLANT_SECTION_PLAN_CHR] = @Plan1

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-14 : 09:25:31

ALTER TABLE tbname DROP COLUMN Dept3

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-14 : 09:25:48

I hate Oracle

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources