Solved

alter table problem

Posted on 2004-09-27
9
302 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
  • 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
 
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
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 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:ScottPletcher
ScottPletcher 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

939 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