Solved

alter table problem

Posted on 2004-09-27
9
303 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 57
cannot connect to sqlserver 8 27
T-SQL: "HAVING CASE" Clause 1 23
SQL View nearest date 5 35
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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