Using SQL Developer to work on SQL Server

[Adam Brown]

Good morning,

I have a SQL Server Database I am working on and only have SQL Developer to work on it and I am coming up with lots of issues to getting some basic stuff to work.

All I am really trying to do is rename a query but it simply will not work, here is what I have tried.

sp_rename 'Contact2', 'Contact4' - which gives me a command unknown error

EXEC sp_rename 'Contact3, 'Contact4' - Incorrect syntax near the keyword 'BEGIN'

ALTER TABLE CONTACT3 RENAME TO CONTACT4 - Incorrect syntax near the keyword 'TO'

ALTER TABLE RENAME CONTACT3 TO CONTACT4 - Incorrect syntax near '.'.

So I figured if I couldn't change the name I could recreate the table so tried

CREATE TABLE CONTACT4 AS (SELECT * FROM CONTACT2); - Incorrect syntax near the keyword 'AS'.

I have tried several other variations but can not remember them all now.

Can anyone please tell me how to rename an SQL Server Table using SQL Developer?

Thanks

[ck9663]

I'm not sure what a SQL Developer is. If you have a SQL Server, shouldn't it be installed with a SSMS of some sort?


~~ CK

[NeoPa]

Is it a query (View) or as table you're trying to rename? Your question indicates both.

[Adam Brown]

SQL Developer is an Oracle SQL Development Environment.

I am trying to change a table.

The problem is that I have a database in SQL Server which depends on having a specific column as the last column and so in order to add a new field I need to take the table and recreate it with the additional column and then drop the original table and rename it. but for some reason I can't get it to rename.

[Rabbit]

1) Why are you using an Oracle product to connect to a Microsoft product?
2) Didn't SQL Server come with SQL Server Management Studio?
3) A database never requires having a column in a certain position. Only processes external to a database object itself would have that kind of restriction. What process is it that you're trying to accomplish?

[Adam Brown]

All very good points I knew someone would ask.

1) My company only uses Oracle databases with this one exception.
2) No, The programme was installed by an outside contractor who did not install SMS.
3) We are using GoldMine CRM, for some reason known only to Frontrange the last column of each table must be the recid column (All columns after this are ignored by the front end) in order to add a new column into the database it is necissary to add it before the recid column, GoldMine can do this itself but this system has all the data on it for 10 years of business and so to add a column from the front end would mean a considerable amount of network strain and in my company this system is a bit of a hot political issue (It was installed without our IT departments consent) it is therefore unlikely to go down well if I start bringing down our batch processes by adding to an already over loaded network.

So whilst I agree with all of your points and they were ones i would have made myself two months ago I find myself stuck using the wrong tool to manipulate a bad system. SQL Developer does state that it includes SQL Server compatability so I am sure it must be possible and I can do most things through SQL Developer (Make Table, Drop Table, Add Fields, Ammend Data) I just can't rename anything (i tried adding a field to the end of the table and renaming it recid but that didn't work either.

[Rabbit]

I have not used GoldMine CRM but have you tried using a view instead? You can use a view to give the illusion of a different column order. This way, you can forego the entire process of make new table, copy over data, drop old table, rename new table.

[Adam Brown]

Unfortunatley it has to be a change to the table otherwise it will not get picked up by the front end.

[Rabbit]

Just a heads up, in your second rename attempt, you're missing a closing quote.

And if you're trying to select everything from one table and put it into another, creating it in the process, the syntax is

SELECT *
INTO newTable
FROM oldTable

[Rabbit]

Not if you use the view as the source for the front end.

[NeoPa]

Adam Brown:
Unfortunatley it has to be a change to the table otherwise it will not get picked up by the front end.

I believe Rabbit's post #10 is in response to this (quoted) statement. I would tend to agree. I see no reason why the front end would have any more difficulty reading from a correctly organised view than it would from a table.

[Adam Brown]

Unfortunatley I can not adjust the front end so it needs to be the contact2 table.

[NeoPa]

That certainly complicates matters for you.

May I suggest, only as a point to consider - I cannot say if this will be fully appropriate in your circumstances, that you provide the view named as [contact2] anyway. This would certainly involve renaming the table in order to avoid confusion, but it might solve this particular problem.

[Adam Brown]

I understand where that could be useful, unfortunatley it is the problem of being unable to rename a table that is causing the issue in the first place.

[NeoPa]

Adam Brown:
unfortunatley it is the problem of being unable to rename a table that is causing the issue in the first place.

Is that manually or in code that you mean? I ask because my suggestion was to do that manually on the server, which should be fairly straightforward I would assume.