Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 859
  • Last Modified:

Script to change nvarchar to varchar for all my tables

I need some T-SQL script to change all "nvarchar" columns to "varchar" for all my tables in my database.  How would I do this?
0
schmir1
Asked:
schmir1
  • 5
  • 3
  • 2
  • +2
5 Solutions
 
Richard QuadlingSenior Software DeveloperCommented:
0
 
BrandonGalderisiCommented:
I assume you are positive that your columns have no unicode data?
0
 
schmir1Author Commented:
Thanks.  I'm trying to run your script in the Server Management Studio but get the following errors:

Msg 5074, Level 16, State 1, Line 1
The object 'UK_principal_name' is dependent on column 'name'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN name failed because one or more objects access this column.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Anthony PerkinsCommented:
You are going to have to do this manually.
0
 
BrandonGalderisiCommented:
Or do a major overhaul on the procedure so that it drops and re-creates the dependent objects.
0
 
csenasaCommented:
Hi,

Try this. It will Change the Nvarchar Columns to Varchar of all tables at one shot!!

NOTE : IF there are Any Key Constraints present on the Column then those columns will fail.
DECLARE @VarQuery VARCHAR(8000)
SELECT  @VarQuery = ISNULL(@VarQuery + ';' , '' ) +  'ALTER TABLE ' + TAble_Name + ' ALTER COLUMN ' 
+   Column_Name + ' VARCHAR(' +  CAST(Character_MAximum_length as VARCHAR(12)) + ') ; '--, * 
FROM Information_schema.columns where Data_type = 'nvarchar'
 
SELECT (@VarQuery)

Open in new window

0
 
BrandonGalderisiCommented:
cesnasa: your script suffers from the same thing that the first posted answer does in that it does not account for dependant objects.  it is already evident that there ARE dependent objects based upon the supplied error.
0
 
schmir1Author Commented:
cesnasa:  I tried it with a test table but it didn't change anything even though this test table didn't have any constraints?  I get the following in the results window.

I'm starting to be convince that I will have to do it manually.  So far my SQL Server experiences are that it is very hard to do anything)-:
ALTER COLUMN Test Format VARCHAR(20) ; ;ALTER TABLE test ALTER COLUMN col1 VARCHAR(50) ; ;ALTER TABLE test ALTER COLUMN col2 VARCHAR(50) ;

Open in new window

0
 
BrandonGalderisiCommented:
Another problem with csenasa's post is that it doesn't take into account the current NULL setting of a column.  You could potentially be changing all of your columns to the default of NULL.
0
 
Anthony PerkinsCommented:
>>I'm starting to be convince that I will have to do it manually<<
I agree.
0
 
BrandonGalderisiCommented:
"I'm starting to be convince that I will have to do it manually"

Manually or through SSMS where it handles a lot of this stuff for you.

JUST KNOW!!!!! that indexes are bound to those columns as well.  And if you are changing the data type of a column, any and all indexes that depend on that table will be rebuilt.  If you have any large tables, that could be time consuming.  

Perhaps you would be best served attempting this in a test environment first.
0
 
schmir1Author Commented:
It took about 3 hours but I did it manually.  Thanks for all your suggestions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now