[RESOLVED] can u help me for a simple quey probelm Sql server 2000

[raghunadhs]

Hi All,
I am practising Sql server 2000.
I have a table called "MetaTable" This "MetaTable" has only one field. that field name is "TableName" . and the records of the table are...

TableName
Student1
Student2
Student3

Here the Student1,Student2 are some other table names. and all these tables will have a field called "Sname". the size of this field is 50. Now i have to write a query in such a way that i should alter the size of "sname" as 70, for all the tablenames.(means, the size of "sname" for student1, student2,student3 should be changed from 50 to 70).

I tried a query as follows.....

ALTERTable (Select tableName from MetaTable) 
   ALTER COLUMN sname Varchar(70)

I know the above statement does not work. but i hope it will give u an idea.
If u know it, Pls help me.

Thanks,
raghunadhs.

[Hack]

ALTER TABLE Student1 MODIFY sName varchar2(70)

[raghunadhs]

Hi Hack,
Thanks for your reply.
The table names "student1", "student2"... will be available in the table called "metaTable". so i need to pick each tableName from "metaTable" and then i have to alter the size of the sname.
SO how can i do the two things in a single query (extracting the table name from "metaTable" and then alter the size of the "sname" for the extracted tablename)?
By using cursors i am able to alter the field size. but i am unable to do it in a single query.

if you know.... pls let me know.....

Thanks in advance...
raghunadhs.

ALTER TABLE Student1 MODIFY sName varchar2(70)

[Hack]

Just run the query three times, once for each table.

[raghunadhs]

Hi Hack,
Ok i can do it like what you have suggested. but... suppose if i don't know which tableNames are reciding in the "metatable", then how can i do it? Actually it is a re-usable query in my Stored procedure. so it should be generic.

Just run the query three times, once for each table.

[szlamany]

You cannot put a sub-query into a DDL statement like ALTER.

You will need to run either a cursor or some other loop to get each table name and then build the ALTER string in the SPROC

and then use EXEC(@sqlstr) to run the ALTER statement.

[raghunadhs]

Thanks Szlamany.
I wrote a cursor. and it is working fine.

regards,
raghunadhs.

You cannot put a sub-query into a DDL statement like ALTER.

You will need to run either a cursor or some other loop to get each table name and then build the ALTER string in the SPROC

and then use EXEC(@sqlstr) to run the ALTER statement.