Solved

alter table problem

Posted on 2004-09-27
9
305 Views
Last Modified: 2012-05-05
Is there a way from Sql Query Analyzer to loop through every table in a database and change a colum called "rowID" to be an identity column in each table?  The column rowID already exists but was not designated as an identity column when it should have been.

I'm sure I'm looking for some kind of "alter table" statement, but can't quite get it to work.

Also -- I don't know how to "loop" through the list of tables.

Thank you.
0
Comment
Question by:gary_j
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 11

Expert Comment

by:calinutz
ID: 12166935
Every serious database has a table called... something like: systables
,which contains all the table names and some properties.
For example Informix has this table called systables.
About altering the table you can always do it using alter table... but the syntax depends on the Database you are using.
Cheers
0
 
LVL 11

Expert Comment

by:calinutz
ID: 12166948
This is an example:

ALTER TABLE yourtable
      ADD CONSTRAINT ( PRIMARY KEY ( rowID) CONSTRAINT idx1 )
0
 
LVL 11

Expert Comment

by:calinutz
ID: 12167234
Place 2 Query components on your form q1, actio. Connect them to your database and then do this

procedure TForm1.FormShow(Sender: TObject);
begin
Q1.Active:=false;
Q1.Sql.Clear;
Q1.Sql.Add('select tabname from systables order by 1');
Q1.Active:=true;
end;


procedure TForm1.Button1Click(Sender: TObject);
var
 i:integer;
 s:string;
begin
q1.FindFirst;
for i:= 1 to Q1.recordcount do
begin
actio.Active:=false;
actio.Sql.Clear;
s:='ALTER TABLE '+q1.FieldByName('tabname').AsString+' ADD CONSTRAINT ( PRIMARY KEY ( rowID) CONSTRAINT idx1 )';
actio.Sql.Add(s);
//actio.ExecSql;
q1.FindNext;
end;
end;


0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12168479
This can be done using SQL only

SELECT UPPER(NAME) FROM SYSOBJECTS
WHERE XTYPE = 'U'

this SQL gives you all tables in the server. create a a look through it and for each table name execute the ALTER TABLE statement.
0
 
LVL 5

Author Comment

by:gary_j
ID: 12169379
ivanov_g

Thank you -- that gets all the tables for me.  I'm a VB guy, though, and don't know how to "loop through" the tables, for example, using SQL Query Analyzer (or a stored procedure).  Also, the ALTER TABLE statements above look to me like they add a column.  I need to alter a column that is currently defined as decimal to make it numeric IDENTITY.

I don't know how this question got into the "Delphi" area -- I thought I put it in the Microsoft SQL Server area!

G
0
 
LVL 12

Accepted Solution

by:
Ivanov_G earned 350 total points
ID: 12169854
it is something like this: (sorry, not tested cause I don't have DB to experiment with it)

-- declare variables
DECLARE @TABLE_NAME VARCHAR(80)

-- declare cursor containing the result from the select aboce
DECLARE TABLES_CURSR CURSOR FOR
SELECT UPPER(NAME) FROM SYSOBJECTS
WHERE XTYPE = 'U'

-- open the cursor
OPEN TABLES_CURSR

-- get the table name in the variable
WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM TABLES_CURSR
  INTO @TABLE_NAME
  -- add whatever you want ...
  -- ALTER TABLE ...
END

-- close the cursor
CLOSE TABLES_CURSR
0
 
LVL 5

Author Comment

by:gary_j
ID: 12169966
Thanks again ivanov_g -- that will work for looping through the tables -- I recognize the "cursor" syntax from previous efforts!  Now all I need to know how to do is the ALTER TABLE statement to change a column property:

ALTER TABLE   (change column-1 from decimal to numeric identity)

G
0
 
LVL 5

Author Comment

by:gary_j
ID: 12170771
when i hit the line
     ALTER TABLE @TABLE_NAME (my code)

I get the error:  Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near '@TABLE_NAME'.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 150 total points
ID: 12171177
You cannot alter a column to an identity column directly in SQL Server.

You can use EM, which will generate and run a script to do it.  You could use that script to create a generalized procedure for doing it for any table and column.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question