Mega Code Archive
How to rename Column in the database engine MS SQL
<![CDATA[
Title: How to rename Column in the database engine MS SQL?
Question: How to rename Column of all tables in the database engine MS SQL simultaniously?
Answer:
Last night, I got a big problem with many tables in the database engine MS SQL Server. Can you
guess what is it? Does it have relation with losing of record? No... it about the name of the column.
How did it make me a crazy? FYI, I have a table for storing information about Vendor. Everytime
this table is inserted a new record. The Insert trigger of the table create new table with name is
the same of the id of the record.
Now, I have 100 records for Vendor. Last night, I decided to rename the field of the table that
already generated. It so crazy if I have to do it manually. And this is not realistic for me to do
that.
Finally, I try to read the documentation of database engine MS SQL Server and found the information
about how to rename object database. From this information, I feel more happy, although still to
think how to use it to rename all the object simultaniously.
Without taking more the time, I have to decide to use cursor for looping to rename, or I have to use
Delphi for renaming it.
I'm sure that both of them can rename all of the tables simultaniously, but I have to consider to use
the right technique in order the process can run smooth.
I think if I use cursor in the database, I have to face about the timeout issue. But this process is more
simple to be done than I have to use delphi to do it.
After I think for several minutes, I had decided to use Delphi to rename the field of the tables. Okay,
by using this board, I 'm going to share to you about how to rename the field of many tables simultaniously.
For the first step, Let you create new project and put some of component to the TForm or TModule.
I recommend you to put TADOConnection, TADOQuery and TButton.
Let you set the connectionstring based on your configuration database. Don't forget to ignore the login
by set the value of LoginPrompt property with False.
Now, Let you create variable as TStringList, if your IDE has installed TMS Instrumentation Workshop, you
can use TVrStringList for store configuration of SQL that we use later.
If you do not have it, you can download it from this board. It already upload and you can download it
by yourself.
For the general, let we use variable TStringList and declare it to private.
[code]
...
private
TempStrings: TStringList;
...
[code]
Now, we create and free this object on onCreate and OnDestroy event.
[code]
...
procedure TForm3.FormCreate(Sender: TObject);
begin
TempStrings := TStringList.Create;
end;
procedure TForm3.FormDestroy(Sender: TObject);
begin
TempStrings.Free;
end;
...
[code]
FYI, In this demo, we use two TADOQueries. And don't forget to set the connection property to
TADOConnection.
For the first TADOQuery, we will collect the information of the name of table in database. So, we
have to write the SQL Script to get them. To get the name of tables, let you set the SQL property
from TADOQuery1 with this script.
[code]
SELECT NAME
FROM SYS.TABLES
[code]
If we execute the script above, we will get the name of tables that have created in the database.
For the next step, we will assign the SQL scriipt to the variable(TempStrings). I want to tell you that
this variable we used as the template, so we do not need type the code so much. Okay, let you
see the SQL Script below:
[code]
...
procedure TForm3.FormCreate(Sender: TObject);
begin
TempStrings := TStringList.Create;
TempStrings.Add('IF EXISTS (');
TempStrings.Add('SELECT NAME');
TempStrings.Add('FROM SYS.COLUMNS');
TempStrings.Add('WHERE OBJECT_ID = OBJECT_ID(''%S'')');
TempStrings.Add('AND NAME = ' + QuotedStr(Edit1.Text));
TempStrings.Add(' )');
TempStrings.Add(' BEGIN');
TempStrings.Add('');
TempStrings.Add(' EXEC SP_RENAME ''%S.%S'', ''%S'',''COLUMN'';');
TempStrings.Add('');
TempStrings.Add(' END');
end;
...
[code]
Now, it's time to type the code for renaming. Please you double click your TButton and type the code
like this :
[code]
...
procedure TForm3.Button1Click(Sender: TObject);
begin
with ADOQuery1 do
begin
Close;
Open;
First;
while not Eof do
begin
ADOQuery2.SQL.Text := Format(TempStrings.Text, [ADOQuery1Name.AsString, ADOQuery1Name.AsString, Edit1.Text, Edit2.Text]);
ADOQuery2.ExecSQL;
Next;
end;
end;
end;
...
[code]
I'm forgot to ask you to put TEdit. If you have not put it to the TForm, let you put it for two Tedits.
It must have to know that this demo project does not work propertly before you set the correct connection string.
So, before you build this project, run the application and execute the process, As I said before that you must
set the connectionstring first.
I hope this article is usefull, so you can use it to help your routine to rename the field of the tables
simultaniously.
If you get something troubles or you have confused after read this article, you can ask it by posting this
thread.
if you interest it and want this source code, let you PM me.
Regards,
Eko Indriyawan
]]>