Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

alter table problem

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
gary_j
Asked:
gary_j
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
calinutzCommented:
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
 
calinutzCommented:
This is an example:

ALTER TABLE yourtable
      ADD CONSTRAINT ( PRIMARY KEY ( rowID) CONSTRAINT idx1 )
0
 
calinutzCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Ivanov_GCommented:
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
 
gary_jAuthor Commented:
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
 
Ivanov_GCommented:
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
 
gary_jAuthor Commented:
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
 
gary_jAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now