Solved

alter table problem

Posted on 2004-09-27
9
306 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 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