December 1, 2009 at 2:03 pm
Should the code look like this if i write in a stored procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_covertdatatype
@tablename
AS
BEGIN
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'
AND name = @tablename ;
SET NOCOUNT ON;
END
GO
December 1, 2009 at 2:11 pm
Few things. You're going to get a syntax error from that saying that the name column is ambiguous. You need to specify the table and column name in the piece you added to the where clause. Look at the rest for an example.
As is, that will just select the statement, it won't actually do any changes. To do the change, define a local variable, assign the built up string to that and then EXEC it. Rough example that you can adapt
DECLARE @s-2 VARCHAR(500)
SELECT @s-2 = 'This is part of a string' + SomeColumn FROM SomeTable
EXEC (@s)
Don't name procedures sp_. sp_ means system procedure and there are some different rules about where SQL checks for stored procedures with that form of name. Basically it's only for the built-in stored procedures.
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 2:50 pm
GilaMonster (12/1/2009)
Few things. You're going to get a syntax error from that saying that the name column is ambiguous. You need to specify the table and column name in the piece you added to the where clause. Look at the rest for an example.As is, that will just select the statement, it won't actually do any changes. To do the change, define a local variable, assign the built up string to that and then EXEC it. Rough example that you can adapt
DECLARE @s-2 VARCHAR(500)
SELECT @s-2 = 'This is part of a string' + SomeColumn FROM SomeTable
EXEC (@s)
Don't name procedures sp_. sp_ means system procedure and there are some different rules about where SQL checks for stored procedures with that form of name. Basically it's only for the built-in stored procedures.
How can i specify the table name and column name. Table name should be taken dynamically. The store procedure should take the table name as a parameter.
As i am very much new to this technology i am unable to understand.
If it is possible please explain me in detail
December 1, 2009 at 3:10 pm
Gotta tell you, between this and the last question about SET IDENTITY INSERT in a trigger, I'm a bit frightened about what is going on in your database. Are you sure you're understanding your boss this time? Changing the size of nvarchar fields should not normally be a routine occurrence.
December 1, 2009 at 4:25 pm
Hi seth, how are you man?
Yes i am sure this time
i have to write a stored procedure so that it can change column datatypes which is char to nchar and varchar to nvarchar as told by my boss
I already mentioned that table name should be taken as a parameter.
December 1, 2009 at 4:51 pm
My fault, I read this one a bit too quickly. I thought you were going to be constantly altering the lengths of the fields. I can definitely see situations where you'd need to change to unicode types. Sorry for jumping to conclusions.
Here is your code with the modifications Gail is talking about: 3 changes. I'm storing the string into a variable so it can be executed at the bottom (until now you were just showing what to do to the table, not actually doing it). I changed the name from sp to usp(never start your sp's with sp_). I aliased the field she was talking about so that it doesn't cause an ambiguity error.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE usp_covertdatatype
@tablenamevarchar(200)
AS
BEGIN
DECLARE @sql varchar(8000)
SET @sql = ''
select @sql = @sql + char(10) + char(13) + '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'
AND tables.name = @tablename ; -- name is aliased as tables.name
SELECT @sql --Shows you which fields you are modifying.
EXEC(@SQL) -- Executes your statement
END
GO
December 2, 2009 at 12:15 am
rahulsony111 (12/1/2009)
How can i specify the table name and column name.
Like this. As I said, look at the rest of the query where it is done properly
BEGIN
select 'alter table ' + sysusers.name + '.' + tables.name
You cannot just refer to the name column alone, there are several tables that have a column with that name. You need to use 2-part naming, <table name>.<column name>
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 2, 2009 at 1:34 am
BEGIN
select 'alter table ' + QUOTENAME(sysusers.name) + '.' + QUOTENAME(tables.name)
Just in case...
_____________
Code for TallyGenerator
December 2, 2009 at 7:18 am
Thanks for all the suggestions and code
I appreciate your help very much
Seth if it is possible can you explain me in detail what is the code doing. Is that taking table name dynamically
December 2, 2009 at 8:19 am
I thought I did? I made very slight modifications to the code you posted(based off of Gus') above and explained all of them.
December 2, 2009 at 11:14 am
In the code you gave above, in the select statement
select @sql = @sql + char(10) + char(13) + 'alter table ' + sysusers.name + '.' + tables.name
+ ' alter column ' + columns.name +
will the tables.name be replaced by @tablename(parameter)
if i pass a table name like tablenew1 to the @tablename like 'EXECUTE usp_covertdatatype tablenew1'
will the select statement selects the tablenew1?
like 'select altertable tablenew1 alter column column'
And will it check for each column of that table whether datatype is char or varchar and change to nchar or nvarchar?
December 2, 2009 at 12:12 pm
rahulsony111 (12/2/2009)
if i pass a table name like tablenew1 to the @tablename like 'EXECUTE usp_covertdatatype tablenew1'will the select statement selects the tablenew1?
like 'select altertable tablenew1 alter column column'
And will it check for each column of that table whether datatype is char or varchar and change to nchar or nvarchar?
Why don't you test it out and see? Just comment out the like that starts with EXEC (so that it won't run any conversions) then run the proc passing various tablenames and see what script it produces.
You should always test for yourself anything you get from the internet, make sure that you understand exactly what it does, don't take someone else's word for it.
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 2, 2009 at 1:10 pm
GilaMonster (12/2/2009)
rahulsony111 (12/2/2009)
if i pass a table name like tablenew1 to the @tablename like 'EXECUTE usp_covertdatatype tablenew1'will the select statement selects the tablenew1?
like 'select altertable tablenew1 alter column column'
And will it check for each column of that table whether datatype is char or varchar and change to nchar or nvarchar?
Why don't you test it out and see? Just comment out the like that starts with EXEC (so that it won't run any conversions) then run the proc passing various tablenames and see what script it produces.
You should always test for yourself anything you get from the internet, make sure that you understand exactly what it does, don't take someone else's word for it.
As you said i tested the code making EXEC as a comment.
i gave this command
EXECUTE usp_convertdatatype DWT40002_ORD_LN
My result is
alter table dbo.DWT40002_ORD_LN alter column ITEM_TYPE nvarchar(50); alter table dbo.DWT40002_ORD_LN alter column LN_TYPE nvarchar(50);
As per the requirements i got the result.
There are only two columns with datatype varchar and they are changes to nvarchar
Everything went fine but i didn't understand part of the code.
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'
AND tables.name = @tablename ;
Can you explain me what this code does here
And now i only selected.
In order to commit should i write EXEC to make changes permanently
December 2, 2009 at 1:20 pm
The "case length" portion doesn't apply to SQL 2000. It was put in when I was writing it for SQL 2005. SQL 2005 has varchar(max) and nvarchar(max) data types, and uses the -1 length to indicate that.
- 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 3, 2009 at 1:53 pm
Even though there is char(10)+char(13) in select statement, i am getting output i one line
May i know the reason
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply