Insert Primary Keys to all tables in a database

I have a database of 300 tables that needed to insert Primary Key:
1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
RPT_NAME.
Is there a fast way to insert it once and for all rather than inserting
it one by one?
Another issue is to change the width of field named COST_CENTR of
VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
Currently using MsSQL 2K. Help is greatly appreciated. ThanksYou can speed this up by scripting.
You can derive every table and column name from various system calls, e.g
use informationschema for tables and
exec sp_columns 'myTable' will list the column names.
You could add some logic to check the coloumn names and then perform an
action , such as add PK
A similar process as above for your second problem - check for column
COST_CENTR and do an ALTER TABLE to widen the col width
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"ymcj" <june.ymc@.gmail.com> wrote in message
news:1145520225.026547.254790@.u72g2000cwu.googlegroups.com...
> I have a database of 300 tables that needed to insert Primary Key:
> 1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
> 2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
> RPT_NAME.
> Is there a fast way to insert it once and for all rather than inserting
> it one by one?
> Another issue is to change the width of field named COST_CENTR of
> VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
> Currently using MsSQL 2K. Help is greatly appreciated. Thanks
>|||On 20 Apr 2006 01:03:45 -0700, ymcj wrote:

>I have a database of 300 tables that needed to insert Primary Key:
>1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
>2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
>RPT_NAME.
Hi ymcj,
This is a very strange request. If alll tables share the same primary
key, then why is your data spread out over 300 tables? The idea of
normalization is to use different tables for data that needs DIFFERENT
keys.
Can you elaborate a bit on what you're trying to achieve and why?
(snip)
>Another issue is to change the width of field named COST_CENTR of
>VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
Execute the following SQL in Query Analyzer (using the results to text
option instead of the results to grid option!!)
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + '
' + 'ALTER COLUMN ' + COLUMN_NAME + ' varchar(12)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'COST_CENTR'
Check the result. If it looks okay, select it, copy and paste it to the
query window and execute it.
Test this on a test database first. After that, make sure that you
perform this task during scheduled down time, and make sure that you
have a good backup.
Hugo Kornelis, SQL Server MVP