Hi,
I have got approximately 100 tables. Their all attributes are same. But, I w
ant to change their one attribute.
Such as my table;
NAME nvarchar(50)
SNAME nvarchar(50)
SALARY float
I want to change SALARY's property from float to money. It is possible to th
is operation manually. But I need to this operation automaticallay via progr
amming language or via query analizer.
How can I do?Why would you want 100 identical tables? How about combining them into one.
If you have no choice but to support the current design then use a query out
of INFORMATION_SCHEMA.TABLES to generate the script.
SELECT 'ALTER TABLE '+
QUOTENAME(table_schema)+'.'+
QUOTENAME(table_name)+' ALTER COLUMN salary DECIMAL(10,2)'
FROM information_schema.tables
WHERE table_name LIKE ?
Now just cut and paste the results into Query Analyzer.
I recommend you avoid using MONEY for the salary column because MONEY has
some problems with precision in arithmetic operations. Use DECIMAL/NUMERIC
instead.
David Portas
SQL Server MVP
--|||Hi
I would do it slightly differently!!!
SELECT 'ALTER TABLE '+
QUOTENAME(table_schema)+'.'+
QUOTENAME(table_name)+' ALTER COLUMN ' + QUOTENAME(COLUMN_NAME) + '
DECIMAL(10,2)'
FROM information_schema.columns
WHERE COLUMN_NAME = 'Salary'
John
"David Portas" wrote:
> Why would you want 100 identical tables? How about combining them into one
.
> If you have no choice but to support the current design then use a query o
ut
> of INFORMATION_SCHEMA.TABLES to generate the script.
> SELECT 'ALTER TABLE '+
> QUOTENAME(table_schema)+'.'+
> QUOTENAME(table_name)+' ALTER COLUMN salary DECIMAL(10,2)'
> FROM information_schema.tables
> WHERE table_name LIKE ?
> Now just cut and paste the results into Query Analyzer.
> I recommend you avoid using MONEY for the salary column because MONEY has
> some problems with precision in arithmetic operations. Use DECIMAL/NUMERIC
> instead.
> --
> David Portas
> SQL Server MVP
> --
>
>
Thursday, March 22, 2012
Changing table properties
Labels:
approximately,
attribute,
attributes,
changing,
database,
microsoft,
mysql,
oracle,
properties,
server,
sql,
table,
tablename,
tables
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment