December 1, 2009 at 12:02 pm
As i said i have to create a MS SQL Server stored procedure that converts a tables column data type from char to nchar or varchar to nvarchar. The stored procedure should take a table name as a parameter.
alter table [tablename] alter column [column name + data type]
Sample: ALTER TABLE DWT40001_ORD_HDR ALTER COLUMN ORD_CTGRY_CD nvarchar(50);
I have a script for this. I just want to know whether this is correct or not
Can someone explain me what the issue is here?
Just explain please
December 1, 2009 at 12:05 pm
Not sure what you mean by "what the issue is here".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2009 at 12:25 pm
That is the task given to me.
I understood some things in that like
I have to create a stored procedure that converts those data types.
BUt i didn't get how can a we take a table name as parameter in stored procedure.
what does that mean? (Does that mean dynamically)
December 1, 2009 at 12:44 pm
Dynamic SQL. No other practical way. Watch out for SQL injection....
Question I have is why a stored procedure? Are you expecting data type changes to happen so often that you want a stored proc to run to fix them? What's wrong with just scripting the changes when they are necessary and running them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 12:45 pm
Instead of doing it with a parameter for the table name, if you really need to do a whole database all at once, try this:
declare Cmd cursor local fast_forward for
select 'alter table ' + schemas.name + '.' + tables.name
+ ' alter column ' + columns.name +
case system_type_id
when 167 then ' nvarchar('
when 175 then ' nchar('
end +
case max_length
when -1 then 'max'
else cast(max_length as varchar(5))
end
+ ');'
from sys.tables
inner join sys.columns
on tables.object_id = columns.object_id
inner join sys.schemas
on tables.schema_id = schemas.schema_id
where system_type_id in (167, 175);
open Cmd;
declare @Cmd nvarchar(1000);
fetch next from Cmd
into @Cmd;
while @@fetch_status = 0
begin
exec(@Cmd);
fetch next from Cmd
into @Cmd;
end;
close Cmd;
deallocate Cmd;
Test that in a database that you can mess with, make sure it does what you need.
Edit: I just realized that what I wrote is for SQL 2005. You'll need to use the SQL 2000 system tables instead.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2009 at 12:55 pm
i should use a stored procedure cause it will be used very often
December 1, 2009 at 1:01 pm
rahulsony111 (12/1/2009)
i should use a stored procedure cause it will be used very often
Once converted, it would not need done again. Just curious, why would this be something that is needed very often?
December 1, 2009 at 1:02 pm
rahulsony111 (12/1/2009)
i should use a stored procedure cause it will be used very often
Why would you be doing data type changes very often?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 1:06 pm
i have no idea why the data types will be changing so often
I was told to do so by my boss.
He just explained me waht to do but didn't say why to do
December 1, 2009 at 1:06 pm
Even with a database with many tables and many columns in each table, the number of changes is limited. Even more limited when considering converting to nchar, nvarchar etc.
I could only see the data type change as being something done "very often" in a development type environment where the database design has yet to be determined. Occasional data type changes might be expected, and could be minimal with good DB design in the beginning.
By using a proc, my concern would shift to source control. What would your method for source control be if you used a proc to make all of your data-type changes? When scripting out the table changes, those could easily be stored in source control.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 1, 2009 at 1:08 pm
GSquared (12/1/2009)
Instead of doing it with a parameter for the table name, if you really need to do a whole database all at once, try this:
declare Cmd cursor local fast_forward forselect 'alter table ' + schemas.name + '.' + tables.name
+ ' alter column ' + columns.name +
case system_type_id
when 167 then ' nvarchar('
when 175 then ' nchar('
end +
case max_length
when -1 then 'max'
else cast(max_length as varchar(5))
end
+ ');'
from sys.tables
inner join sys.columns
on tables.object_id = columns.object_id
inner join sys.schemas
on tables.schema_id = schemas.schema_id
where system_type_id in (167, 175);
open Cmd;
declare @Cmd nvarchar(1000);
fetch next from Cmd
into @Cmd;
while @@fetch_status = 0
begin
exec(@Cmd);
fetch next from Cmd
into @Cmd;
end;
close Cmd;
deallocate Cmd;
Test that in a database that you can mess with, make sure it does what you need.
Edit: I just realized that what I wrote is for SQL 2005. You'll need to use the SQL 2000 system tables instead.
The code seems to be very tough for me as i am beginner in this.
Anyways thanks for giving this
I will try to understand
December 1, 2009 at 1:15 pm
What it does is declare a cursor that select the table and column and size, from the system tables. It builds a command to alter the column into either nchar or nvarchar, and keep the size the same. (That may be a problem if any of the columns are wider than 4000 characters.)
Then it steps through the cursor and issues the commands, one at a time, for the whole database.
If you want to see what it does, just run the select statement:
select 'alter table ' + schemas.name + '.' + tables.name
+ ' alter column ' + columns.name +
case system_type_id
when 167 then ' nvarchar('
when 175 then ' nchar('
end +
case max_length
when -1 then 'max'
else cast(max_length as varchar(5))
end
+ ');'
from systables tables
inner join syscolumns columns
on tables.object_id = columns.object_id
inner join sysschemas schemas
on tables.schema_id = schemas.schema_id
where system_type_id in (167, 175);
(I modified this. I think it will work with SQL 2000, but don't have a server I can test it on.)
That will give you the list of commands it is running. That should help you see how it works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2009 at 1:31 pm
This should work on SQL 2000
select 'alter table ' + sysusers.name + '.' + tables.name
+ ' alter column ' + columns.name +
case columns.xtype
when 167 then ' nvarchar('
when 175 then ' nchar('
end +
case columns.length
when -1 then 'max'
else cast(length as varchar(5))
end
+ ');'
from sysobjects tables
inner join syscolumns columns
on tables.id = columns.id
inner join sysusers
on tables.uid = sysusers.uid
where columns.xtype in (167, 175)
AND tables.xtype = 'U';
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 1:38 pm
The code is working. You are awesome
But i need a stored procedure for this.
I think the code you gave is a one-time process
The problem is that i have do this dynamically, taking table name as a parameter
December 1, 2009 at 1:42 pm
Add the parameter, and then add that to the Where clause for the query. "Where name = @TableName", assuming "@TableName" is the name of the parameter.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply