Q:

recreating whole SQL Server Database with relations

i have used SQL server 2012 and made a huge database with many relations. today i realized using "NCHAR" instead on "NVARCHAR" adds space on end of strings which i don't like it. is there any way to recreate database with relations. I've tried "drop and create" for tables but needs to remove all relations and define them again which is very time consuming.

A:

It's possible to alter only the affected tables? like this:
SELECT 'alter table '+s.name+'.'+t.name+
' alter column '+c.name+' nvarchar('+convert(varchar(11),c.max_length/2)+') go'
FROM    sys.tables as T
inner join sys.schemas as s
on T.[schema_id]=s.[schema_id]
inner join sys.columns as C
on T.[object_id]=C.[object_id]
and c.system_type_id=239--nchar type

, and before that don't forget to use the same kind of mechanism to generate an update statement to remove extra spaces.

