I want to write a script for SQL Server, which changes the table from ASCII to UNICODE.For that i need to change Char(n) to nchar(n), similarly for varchar(n) to NVarchar(n).But i am not able to find out the value of n. Can anybody tell me, how can I get the value of n.
Thanks In Advance
try this:
SELECT sh.name+'.'+o.name AS ObjectName ,o.type_desc AS ObjectType ,s.name as ColumnName ,CASE WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')' WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')' WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')' ELSE t.name END AS DataType ,CASE WHEN s.is_nullable=1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable ,CASE WHEN ic.column_id IS NULL THEN '' ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null') END +CASE WHEN sc.column_id IS NULL THEN '' ELSE ' computed('+ISNULL(sc.definition,'')+')' END +CASE WHEN cc.object_id IS NULL THEN '' ELSE ' check('+ISNULL(cc.definition,'')+')' END AS MiscInfo FROM sys.columns s INNER JOIN sys.types t ON s.system_type_id=t.system_type_id and t.is_user_defined=0 INNER JOIN sys.objects o ON s.object_id=o.object_id INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id LEFT OUTER JOIN sys.identity_columns ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id LEFT OUTER JOIN sys.computed_columns sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id WHERE o.name='YourTableName' order by 1,s.column_id 
Never used it, but by quickly googling around:
sp_columns <table_name> ?
Look at all the nice things in your INFORMATION_SCHEMA. Lots of views, and some even contain what you ask for ;)
--For the value of 'n' use: Select Character_Maximum_Length AS [Size] FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'yourTable' AND COLUMN_NAME = 'yourColumn' 