Changing table colon's properties

Hi,
I have got approximately 100 tables. Their all attributes are same. But, I
want 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
programming language or via query analizer.
How can I do?huseyin_akturk wrote:
> Hi,
> I have got approximately 100 tables. Their all attributes are same.
> But, I want 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 programming language or via query analizer.
> How can I do?
You can add a new column Salary2 Money, Update the table and move all
the float values into the new money type (cast the value and round as
needed), drop the old column, and then rename the new column. Test in
dev before applying to production. You may also want to consider using a
DECIMAL(X) or INT for the salary, especially if you want a fixed number
of decimal places (say 2) or do not require decimal places at all.
The following example moves the salary values directly from one column
to the other.
Create Table dbo.SalaryTestXXX (
MyID INT IDENTITY NOT NULL PRIMARY KEY,
Salary FLOAT NULL )
go
Insert Into dbo.SalaryTestXXX Values (25000)
Insert Into dbo.SalaryTestXXX Values (50000)
Insert Into dbo.SalaryTestXXX Values (75000)
Insert Into dbo.SalaryTestXXX Values (100000)
Select * from dbo.SalaryTestXXX
go
Alter Table dbo.SalaryTestXXX
Add Salary2 Money
go
Select * from dbo.SalaryTestXXX
Update dbo.SalaryTestXXX
Set Salary2 = Salary
Where Salary IS NOT NULL
go
Select * from dbo.SalaryTestXXX
go
Alter Table dbo.SalaryTestXXX
Drop Column Salary
go
Select * from dbo.SalaryTestXXX
Exec sp_rename 'dbo.SalaryTestXXX.Salary2', 'Salary', 'COLUMN'
Select * from dbo.SalaryTestXXX
Drop Table dbo.SalaryTestXXX
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Are you sure you want to change it to MONEY? MONEY data type might not be
exactly what you think it is. You might want to use NUMERIC(18, 2) or
NUMERIC(18, 4) or some other variation of NUMERIC instead of MONEY... MONEY
stores currency display information in the same column as the value, which
can cause headaches.
"huseyin_akturk" <huseyinakturk@.discussions.microsoft.com> wrote in message
news:7C2B0E2B-FEA8-4BA2-8777-CC1D0DED37E3@.microsoft.com...
> Hi,
> I have got approximately 100 tables. Their all attributes are same. But, I
> want 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
> programming language or via query analizer.
> How can I do?
>|||See if this helps:
use tempdb
go
create table t1 (
salary float
)
go
insert into t1 values(1245.07)
go
select * from t1
go
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cn sysname
declare columns_cursor cursor local fast_forward
for
select
table_schema,
table_name
from
information_schema.columns
where
column_name = 'salary'
and data_type = 'float'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
open columns_cursor
while 1 = 1
begin
fetch next from columns_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'alter table ' + quotename(@.ts) + '.' + quotename(@.tn) + N'
alter column salary money'
print @.sql
exec sp_executesql @.sql
end
close columns_cursor
deallocate columns_cursor
go
select * from t1
go
drop table t1
go
AMB
"huseyin_akturk" wrote:

> Hi,
> I have got approximately 100 tables. Their all attributes are same. But, I
> want 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
> programming language or via query analizer.
> How can I do?
>