September 7, 2015 at 1:52 am
Hi Team,
I have a list of 100+ tables in my SQL Database with most of the columns are declared as VARCHAR, I want to create the same 100+ tables in new environment with same structure but Varchar datatype should be NVARCHAR.
How to generate an automatic table scripts with new datatype (NVARCHAR) for all the tables.
Please suggest.
September 7, 2015 at 2:19 am
Script the tables out and use Find and Replace in Management Studio to replace nvarchar with varchar.
John
September 7, 2015 at 4:36 am
Create the scripts for the tables first. Then modify those scripts prior to running them. Search and replace should work fine as was already mentioned.
A second suggestion, use this as the time to also get your code into source control.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 7, 2015 at 5:26 am
September 7, 2015 at 5:52 am
No, if you change, for example, varchar(100) to nvarchar(100), the column width increases from 100 bytes to 200, but the only thing that changes in the column definition is the additional "n". However, since the requirement was for automatic generation of the CREATE TABLE statements, you might want to write scripts in any case - you can query INFORMATION_SCHEMA.COLUMNS (but beware that the schema name is not reliable if you do that) or the catalog views (sys.columns etc).
John
September 7, 2015 at 5:59 am
Jayanth_Kurup (9/7/2015)
Find and replace will change the varchar to nvarchar but you still need to account for the increase in the column width now. I think dynamic sql might be the better option.
What increase in column width? OP asked just to change varchar to nvarchar,, no mention of changing the length of the columns as well
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
September 7, 2015 at 6:11 am
Interesting. Spent an little bit of time knocking this up based on Lowell's script that was uploaded here --> http://www.sqlservercentral.com/scripts/SQL+Server+2005/67515/%5B/url%5D
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
BEGIN
DROP TABLE #indexes;
END;
SELECT CASE WHEN [a].[type_desc] <> 'HEAP'
THEN CASE WHEN [a].[is_primary_key] = 1
OR [a].[is_unique] = 1
THEN CHAR(13) + CHAR(10) + 'CONSTRAINT ['
+ [a].[index_name] + '] '
+ CASE WHEN [a].[is_primary_key] = 1
THEN ' PRIMARY KEY '
ELSE CASE WHEN [a].[is_unique] = 1
THEN ' UNIQUE '
ELSE ''
END
END + [a].[type_desc] COLLATE Latin1_General_CI_AS
+ CASE WHEN [a].[type_desc] = 'NONCLUSTERED'
THEN ''
ELSE ' '
END + ' (' + [a].[index_columns_key] + ')'
+ CASE WHEN [a].[index_columns_include] <> '---'
THEN ' INCLUDE ('
+ [a].[index_columns_include] + ')'
ELSE ''
END
+ CASE WHEN [a].[fill_factor] <> 0
THEN ' WITH FILLFACTOR = '
+ CONVERT(VARCHAR(30), [a].[fill_factor])
ELSE ''
END + ','
ELSE CHAR(13) + CHAR(10) + 'CREATE INDEX ['
+ [a].[index_name] + '] ' + ' ON ['
+ [a].[object_name] + ']' + ' ('
+ [a].[index_columns_key] + ')'
+ CASE WHEN [a].[index_columns_include] <> '---'
THEN ' INCLUDE ('
+ [a].[index_columns_include] + ')'
ELSE ''
END
+ CASE WHEN [a].[fill_factor] <> 0
THEN ' WITH FILLFACTOR = '
+ CONVERT(VARCHAR(30), [a].[fill_factor])
ELSE ''
END + ';'
END
ELSE ''
END AS [INDEX_DEF],
CASE WHEN [a].[type_desc] <> 'HEAP'
THEN CASE WHEN [a].[is_primary_key] = 1
OR [a].[is_unique] = 1 THEN 'CONSTRAINT'
ELSE 'INDEX'
END
ELSE ''
END AS [TYPE],
[a].[object_id]
INTO #indexes
FROM ( SELECT [conObj].[object_id],
[conObj].[name] AS object_name,
[conInde].[index_id],
ISNULL([conInde].[name], '---') AS index_name,
[partitions].[Rows],
[partitions].[SizeMB],
INDEXPROPERTY([conObj].[object_id], [conInde].[name],
'IndexDepth') AS IndexDepth,
[conInde].[type],
[conInde].[type_desc],
[conInde].[fill_factor],
[conInde].[is_unique],
[conInde].[is_primary_key],
[conInde].[is_unique_constraint],
ISNULL([Index_Columns].[index_columns_key], '---') AS index_columns_key,
ISNULL([Index_Columns].[index_columns_include], '---') AS index_columns_include
FROM [sys].[objects] conObj
JOIN [sys].[indexes] conInde ON [conObj].[object_id] = [conInde].[object_id]
JOIN ( SELECT [object_id],
index_id,
SUM(row_count) AS Rows,
CONVERT(NUMERIC(19, 3), CONVERT(NUMERIC(19, 3), SUM(in_row_reserved_page_count
+ lob_reserved_page_count
+ row_overflow_reserved_page_count))
/ CONVERT(NUMERIC(19, 3), 128)) AS SizeMB
FROM [sys].[dm_db_partition_stats]
GROUP BY [object_id],
index_id
) AS partitions ON [conInde].[object_id] = [partitions].[object_id]
AND [conInde].[index_id] = [partitions].[index_id]
CROSS APPLY ( SELECT LEFT([Index_Columns].[index_columns_key],
LEN([Index_Columns].[index_columns_key])
- 1) AS index_columns_key,
LEFT([Index_Columns].[index_columns_include],
LEN([Index_Columns].[index_columns_include])
- 1) AS index_columns_include
FROM ( SELECT ( SELECT [name] + ',' + ' '
FROM [sys].[index_columns]
JOIN [sys].[columns] ON [Index_Columns].[column_id] = [columns].[column_id]
AND [Index_Columns].[object_id] = [columns].[object_id]
WHERE [is_included_column] = 0
AND [conInde].[object_id] = [Index_Columns].[object_id]
AND [conInde].[index_id] = [index_id]
ORDER BY key_ordinal
FOR
XML PATH('')
) AS index_columns_key,
( SELECT [name] + ',' + ' '
FROM [sys].[index_columns]
JOIN [sys].[columns] ON [Index_Columns].[column_id] = [columns].[column_id]
AND [Index_Columns].[object_id] = [columns].[object_id]
WHERE [is_included_column] = 1
AND [conInde].[object_id] = [Index_Columns].[object_id]
AND [conInde].[index_id] = [index_id]
ORDER BY index_column_id
FOR
XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
) a;
IF OBJECT_ID('tempdb..#tables') IS NOT NULL
BEGIN
DROP TABLE #tables;
END;
SELECT SCHEMA_NAME([tab].[schema_id]) AS 'Schema',
[tab].[name] AS 'Name',
ISNULL([prop].[value], '') AS 'Description',
SUBSTRING([creat].[def], 1, LEN([creat].[def]) - 1) + ');' + CHAR(13)
+ CHAR(10) + ISNULL([ind].[def], '') + ISNULL([trig].[def], '')
+ ISNULL(CHAR(13) + CHAR(10) + [extabprop].[def], '') AS 'Definition',
[tab].[object_id]
INTO #tables
FROM [sys].[objects] tab
CROSS APPLY ( SELECT ( (SELECT CASE WHEN [col].[is_computed] = 1
THEN CHAR(13) + CHAR(10) + '['
+ UPPER([col].[name]) + '] '
+ 'AS ' + UPPER([col].[name])
ELSE CHAR(13) + CHAR(10) + '['
+ UPPER([col].[name]) + '] '
+ UPPER(CASE WHEN TYPE_NAME([col].[system_type_id]) = 'VARCHAR'
THEN 'NVARCHAR'
ELSE TYPE_NAME([col].[system_type_id])
END)
+ CASE WHEN TYPE_NAME([col].[system_type_id]) IN (
'decimal', 'numeric' )
THEN '('
+ CONVERT(VARCHAR, [col].[precision])
+ ','
+ CONVERT(VARCHAR, [col].[scale])
+ ') '
+ CASE
WHEN [col].[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
WHEN TYPE_NAME([col].[system_type_id]) IN (
'float', 'real' )
THEN CASE
WHEN [col].[precision] = 53
THEN CASE
WHEN [col].[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR, [col].[precision])
+ ') '
+ CASE
WHEN [col].[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
WHEN TYPE_NAME([col].[system_type_id]) IN (
'char', 'varchar' )
THEN CASE
WHEN [col].[max_length] = -1
THEN '(max)'
+ CASE
WHEN [col].[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR, [col].[max_length])
+ ') '
+ CASE
WHEN [col].[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
WHEN TYPE_NAME([col].[system_type_id]) IN (
'nchar', 'nvarchar' )
THEN CASE
WHEN [col].[max_length] = -1
THEN '(max)'
+ CASE
WHEN [col].[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR, ( [col].[max_length]
/ 2 )) + ') '
+ CASE
WHEN [col].[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
WHEN TYPE_NAME([col].[system_type_id]) IN (
'datetime', 'money',
'text', 'image' )
THEN ' '
+ CASE
WHEN [col].[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE +CASE
WHEN COLUMNPROPERTY([col].[object_id],
[col].[name],
'IsIdentity') = 0
THEN ' '
ELSE ' IDENTITY('
+ CONVERT(VARCHAR, ISNULL(IDENT_SEED(OBJECT_NAME([col].[object_id])),
1)) + ','
+ CONVERT(VARCHAR, ISNULL(IDENT_INCR(OBJECT_NAME([col].[object_id])),
1)) + ')'
END
+ CASE
WHEN [col].[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
+ CASE WHEN [col].[default_object_id] = 0
THEN ''
ELSE ' CONSTRAINT ['
+ [DEF].[name]
+ '] DEFAULT '
+ ISNULL([DEF].[definition],
'')
END
END + ','
FROM [sys].[columns] col
LEFT OUTER JOIN [sys].[default_constraints] DEF ON [col].[default_object_id] = [DEF].[object_id]
WHERE [col].[object_id] = [tab].[object_id]
ORDER BY [col].[column_id]
FOR
XML PATH(''),
TYPE
).value('.', 'NVARCHAR(MAX)') )
) cols ( def )
OUTER APPLY ( SELECT ( (SELECT [allCons].[INDEX_DEF]
FROM [#indexes] AS [allCons]
WHERE [allCons].[TYPE] = 'CONSTRAINT'
AND [allCons].[object_id] = [tab].[object_id]
FOR
XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)') )
) cons ( def )
OUTER APPLY ( SELECT ( (SELECT CHAR(13) + CHAR(10)
+ ISNULL('CONSTRAINT [' + [objects].[name]
+ '] ' + ' CHECK '
+ ISNULL([definition], '') + ',',
'')
FROM [sys].[objects]
INNER JOIN [sys].[check_constraints] ON [objects].[object_id] = [check_constraints].[object_id]
WHERE [objects].[type] = 'C'
AND [objects].[parent_object_id] = [tab].[object_id]
FOR
XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)') )
) chec ( def )
OUTER APPLY ( SELECT ( ( SELECT CHAR(13) + CHAR(10) + 'CONSTRAINT ['
+ OBJECT_NAME(constid) + ']'
+ ' FOREIGN KEY (' + COL_NAME(fkeyid,
fkey)
+ ') REFERENCES ['
+ OBJECT_SCHEMA_NAME(rkeyid) + '].['
+ OBJECT_NAME(rkeyid) + ']' + '('
+ COL_NAME(rkeyid, rkey) + '),'
FROM [sys].[sysforeignkeys]
WHERE fkeyid = [tab].[object_id]
FOR
XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)') )
) fk ( def )
OUTER APPLY ( SELECT ( (SELECT [allCons].[INDEX_DEF]
FROM [#indexes] AS [allCons]
WHERE [allCons].[TYPE] = 'INDEX'
AND [allCons].[object_id] = [tab].[object_id]
FOR
XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)') )
) ind ( def )
CROSS APPLY ( SELECT 'CREATE TABLE ['
+ OBJECT_SCHEMA_NAME([tab].[object_id]) + '].['
+ OBJECT_NAME([tab].[object_id]) + '] ( '
+ [cols].[def] + ISNULL([cons].[def], '')
+ ISNULL([chec].[def], '') + ISNULL([fk].[def], '')
) creat ( [def] )
OUTER APPLY ( SELECT ( (SELECT CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ 'GO' + CHAR(13) + CHAR(10)
+ OBJECT_DEFINITION([o].[object_id])
+ CHAR(13) + CHAR(10) + 'GO'
FROM [sys].[objects] o
WHERE [o].[type] = 'TR'
AND [o].[parent_object_id] = [tab].[object_id]
FOR
XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)') )
) trig ( def )
OUTER APPLY ( SELECT ( (SELECT CASE WHEN [a].[minor_id] = 0
THEN '-- Table Description' + CHAR(13)
+ CHAR(10)
+ 'IF EXISTS (SELECT 1 '
+ CHAR(13) + CHAR(10)
+ ' FROM sys.objects '
+ CHAR(13) + CHAR(10)
+ ' WHERE name = '
+ CHAR(39)
+ OBJECT_NAME([tab].[object_id])
+ CHAR(39) + CHAR(13) + CHAR(10)
+ ' AND type = '
+ CHAR(39) + 'U' + CHAR(39)
+ CHAR(13) + CHAR(10)
+ ' )' + CHAR(13)
+ CHAR(10) + 'BEGIN ' + CHAR(13)
+ CHAR(10)
+ ' IF EXISTS (SELECT 1 '
+ CHAR(13) + CHAR(10)
+ ' FROM sys.extended_properties a'
+ CHAR(13) + CHAR(10)
+ ' WHERE a.major_id = OBJECT_ID('
+ CHAR(39)
+ OBJECT_NAME([tab].[object_id])
+ CHAR(39) + ')' + CHAR(13)
+ CHAR(10) + ' )'
+ CHAR(13) + CHAR(10)
+ ' BEGIN ' + CHAR(13)
+ CHAR(10)
+ ' EXEC sp_dropextendedproperty N'
+ CHAR(39) + [a].[name]
+ CHAR(39) + ', N' + CHAR(39)
+ 'USER' + CHAR(39) + ', N'
+ CHAR(39) + 'dbo' + CHAR(39)
+ ', N' + CHAR(39) + 'TABLE'
+ CHAR(39) + ', N' + CHAR(39)
+ OBJECT_NAME([tab].[object_id])
+ CHAR(39) + ';' + CHAR(13)
+ CHAR(10) + ' END;'
+ CHAR(13) + CHAR(10)
+ ' EXEC sp_addextendedproperty @name=N'
+ CHAR(39) + [a].[name]
+ CHAR(39) + ', @value=N'
+ CHAR(39)
+ CAST([a].[value] AS NVARCHAR(MAX))
+ CHAR(39) + ',' + CHAR(13)
+ CHAR(10)
+ ' @level0type=N'
+ CHAR(39) + 'SCHEMA' + CHAR(39)
+ ', @level0name=N' + CHAR(39)
+ 'dbo' + CHAR(39)
+ ', @level1type=N' + CHAR(39)
+ 'TABLE' + CHAR(39)
+ ', @level1name=N' + CHAR(39)
+ OBJECT_NAME([tab].[object_id])
+ CHAR(39) + ';' + CHAR(13)
+ CHAR(10) + 'END;' + CHAR(13)
+ CHAR(10) + 'GO' + CHAR(13)
+ CHAR(10)
ELSE CASE WHEN [a].[name] = 'XNY_PARTITION_COLUMN_ITEM_TYPE'
THEN '-- ' + .[name]
+ ': '
+ CAST([a].[value] AS NVARCHAR(MAX))
+ CHAR(13) + CHAR(10)
WHEN [a].[class] = 1
THEN '-- ' + .[name]
+ CHAR(13) + CHAR(10)
ELSE '-- ' + [c].[name]
+ CHAR(13) + CHAR(10)
END + 'IF EXISTS ( SELECT 1'
+ CHAR(13) + CHAR(10)
+ CASE WHEN [a].[class] = 1
THEN ' FROM sys.columns'
+ CHAR(13) + CHAR(10)
+ ' WHERE OBJECT_NAME(object_id) = '
+ CHAR(39)
+ OBJECT_NAME([tab].[object_id])
+ CHAR(39) + CHAR(13)
+ CHAR(10)
+ ' AND name = '
+ CHAR(39)
+ .[name]
+ CHAR(39) + ' )'
+ CHAR(13) + CHAR(10)
ELSE ' FROM sys.indexes'
+ CHAR(13) + CHAR(10)
+ ' WHERE OBJECT_NAME(object_id) = '
+ CHAR(39)
+ OBJECT_NAME([tab].[object_id])
+ CHAR(39) + CHAR(13)
+ CHAR(10)
+ ' AND name = '
+ CHAR(39)
+ [c].[name]
+ CHAR(39) + ' )'
+ CHAR(13) + CHAR(10)
END + 'BEGIN' + CHAR(13)
+ CHAR(10)
+ ' IF EXISTS ( SELECT 1'
+ CHAR(13) + CHAR(10)
+ ' FROM sys.extended_properties a'
+ CHAR(13) + CHAR(10)
+ CASE WHEN [a].[class] = 1
THEN +' INNER JOIN sys.columns c ON a.major_id = c.object_id'
+ CHAR(13) + CHAR(10)
+ ' AND a.minor_id = c.column_id'
+ CHAR(13) + CHAR(10)
ELSE +' INNER JOIN sys.indexes c ON a.major_id = c.object_id'
+ CHAR(13) + CHAR(10)
+ ' AND a.minor_id = c.index_id'
+ CHAR(13) + CHAR(10)
END
+ ' WHERE a.major_id = OBJECT_ID('
+ CHAR(39)
+ OBJECT_NAME([tab].[object_id])
+ CHAR(39) + ')' + CHAR(13)
+ CHAR(10)
+ ' AND a.name = N'
+ CHAR(39) + [a].[name]
+ CHAR(39) + CHAR(13) + CHAR(10)
+ ' AND c.name = '
+ CHAR(39) + ISNULL(.[name],
[c].[name])
+ CHAR(39) + CHAR(13) + CHAR(10)
+ ' AND a.class = '
+ CAST([a].[class] AS NVARCHAR(2))
+ ' )' + CHAR(13) + CHAR(10)
+ ' BEGIN' + CHAR(13)
+ CHAR(10)
+ ' EXECUTE sys.sp_dropextendedproperty N'
+ CHAR(39) + [a].[name]
+ CHAR(39) + ', N' + CHAR(39)
+ 'USER' + CHAR(39) + ', N'
+ CHAR(39) + 'dbo' + CHAR(39)
+ ',' + CHAR(13) + CHAR(10)
+ ' N' + CHAR(39)
+ 'TABLE' + CHAR(39) + ', N'
+ CHAR(39)
+ OBJECT_NAME([tab].[object_id])
+ CHAR(39) + ', N' + CHAR(39)
+ CASE WHEN [a].[class] = 1
THEN 'COLUMN'
ELSE 'INDEX'
END + CHAR(39) + ',' + CHAR(13)
+ CHAR(10) + ' N'
+ CHAR(39) + ISNULL(.[name],
[c].[name])
+ CHAR(39) + ';' + CHAR(13)
+ CHAR(10) + ' END;'
+ CHAR(13) + CHAR(10)
+ ' EXECUTE sys.sp_addextendedproperty @name = N'
+ CHAR(39) + [a].[name]
+ CHAR(39) + ',' + CHAR(13)
+ CHAR(10)
+ ' @value = N' + CHAR(39)
+ CAST([a].[value] AS NVARCHAR(MAX))
+ CHAR(39) + ',' + CHAR(13)
+ CHAR(10)
+ ' @level0type = N'
+ CHAR(39) + 'SCHEMA' + CHAR(39)
+ ', @level0name = N' + CHAR(39)
+ 'dbo' + CHAR(39)
+ ', @level1type = N' + CHAR(39)
+ 'TABLE' + CHAR(39) + ','
+ CHAR(13) + CHAR(10)
+ ' @level1name = N'
+ CHAR(39)
+ OBJECT_NAME([tab].[object_id])
+ CHAR(39) + ',' + CHAR(13)
+ CHAR(10)
+ ' @level2type = N'
+ CHAR(39)
+ CASE WHEN [a].[class] = 1
THEN 'COLUMN'
ELSE 'INDEX'
END + CHAR(39)
+ ', @level2name = N' + CHAR(39)
+ ISNULL(.[name], [c].[name])
+ CHAR(39) + ';' + CHAR(13)
+ CHAR(10) + 'END;' + CHAR(13)
+ CHAR(10) + 'GO' + CHAR(13)
+ CHAR(10)
END
FROM [sys].[extended_properties] a
OUTER APPLY ( SELECT [col].[name]
FROM [sys].[columns] col
WHERE [a].[major_id] = [col].[object_id]
AND [a].[minor_id] = [col].[column_id]
AND [a].[class] = 1
AND [a].[minor_id] > 0
) b
OUTER APPLY ( SELECT [ind].[name]
FROM [sys].[indexes] ind
WHERE [a].[major_id] = [ind].[object_id]
AND [a].[minor_id] = [ind].[index_id]
AND [a].[class] = 7
AND [a].[minor_id] > 0
) c
WHERE [a].[major_id] = [tab].[object_id]
ORDER BY [a].[class],
[a].[minor_id]
FOR
XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)') )
) extabprop ( def )
OUTER APPLY ( SELECT CAST(value AS NVARCHAR(MAX))
FROM [sys].[extended_properties] a
WHERE a.[major_id] = tab.[object_id]
AND a.[minor_id] = 0
) prop ( value )
WHERE [tab].[type] = 'U'
AND [tab].[name] <> 'dtproperties';
SELECT [base].[Schema] AS '@Schema',
[base].[Name] AS '@Name',
[base].[Description] AS '@Description',
[base].[Definition] AS 'Definition',
columnData.[data] AS 'Columns'
FROM [#tables] base
CROSS APPLY ( SELECT cols.name AS '@Name',
TYPE_NAME([cols].[system_type_id])
+ CASE WHEN TYPE_NAME([cols].[system_type_id]) IN (
'decimal', 'numeric' )
THEN '(' + CONVERT(VARCHAR, [cols].[precision])
+ ',' + CONVERT(VARCHAR, [cols].[scale])
+ ') '
WHEN TYPE_NAME([cols].[system_type_id]) IN (
'float', 'real' )
THEN CASE WHEN [cols].[precision] = 53 THEN ''
ELSE '('
+ CONVERT(VARCHAR, [cols].[precision])
+ ') '
END
WHEN TYPE_NAME([cols].[system_type_id]) IN (
'char', 'varchar' )
THEN CASE WHEN [cols].[max_length] = -1
THEN '(max)'
ELSE '('
+ CONVERT(VARCHAR, [cols].[max_length])
+ ') '
END
WHEN TYPE_NAME([cols].[system_type_id]) IN (
'nchar', 'nvarchar' )
THEN CASE WHEN [cols].[max_length] = -1
THEN '(max)'
ELSE '('
+ CONVERT(VARCHAR, ( [cols].[max_length]
/ 2 )) + ') '
END
WHEN TYPE_NAME([cols].[system_type_id]) IN (
'datetime', 'money', 'text', 'image' )
THEN ''
ELSE +CASE WHEN COLUMNPROPERTY([cols].[object_id],
[cols].[name],
'IsIdentity') = 0
THEN ' '
ELSE ' IDENTITY('
+ CONVERT(VARCHAR, ISNULL(IDENT_SEED(OBJECT_NAME([cols].[object_id])),
1)) + ','
+ CONVERT(VARCHAR, ISNULL(IDENT_INCR(OBJECT_NAME([cols].[object_id])),
1)) + ')'
END
END AS '@Type',
[cols].[max_length] AS '@Length',
CASE WHEN [cols].[is_nullable] = 0 THEN 'False'
ELSE 'True'
END AS '@AllowNulls',
ISNULL([defs].[definition], '') AS '@Default',
ISNULL(CAST(doc.[value] AS VARCHAR(MAX)), '') AS '@Description'
FROM sys.columns cols
LEFT OUTER JOIN [sys].[default_constraints] [defs] ON cols.[default_object_id] = [defs].[object_id]
LEFT OUTER JOIN [sys].[extended_properties] [doc] ON [doc].[major_id] = cols.[object_id]
AND [doc].[minor_id] = cols.[column_id]
AND [doc].[class] = 1
AND [doc].[minor_id] > 0
WHERE cols.[object_id] = base.object_id
FOR
XML PATH('Column'),
TYPE
) columnData ( data )
ORDER BY [base].[Name];
That includes indexes, foreign keys, extended properties, triggers, other constraints etc. Takes about 15 seconds to come back on my dev system.
--EDIT--
Compiled and ran in SQL Server 2014, not 2008R2. Haven't checked the validity very much, just compared with one table on my test bed.
September 7, 2015 at 7:48 am
Minnu (9/7/2015)
Hi Team,I have a list of 100+ tables in my SQL Database with most of the columns are declared as VARCHAR, I want to create the same 100+ tables in new environment with same structure but Varchar datatype should be NVARCHAR.
How to generate an automatic table scripts with new datatype (NVARCHAR) for all the tables.
Please suggest.
Unless all of those tables are just one part of the database, have an identical purpose, and are nearly identical in form, fit, and function, I'd like to recommend not doing this.
Other than the blob datatypes (VARCHAR(MAX) most specifically in this case), any VARCHAR defined over 4000 characters will fail. The size of your database footprint will double. Things like single character "flag" columns may unnecessarily double but will, of course, double either way. Many of your indexes may no longer fit into 900 bytes. Columns such as telephone "numbers" and SSNs will be unnecessary be doubled in byte size. In theory, all of your queries will take twice as long to run because each table page will only be able to hold half the number of rows at the very best. And, of course, since you stated that most of the columns are declared as VARCHAR, you may have a wealth of datatype sins in your database that you're not only going to perpetuate, but double the space requirements for in memory, on disk, and in your backups. Then, there's any dynamic SQL that may be involved not to mention possibly changing objects and code in managed code if any is present. I can only speculate as to what it will do to any reporting software that you're running against those tables.
If you have already planned for such things, then good enough. If not, then you need to because a whole lot of things are going to change other than just the datatypes in the columns in the tables. Lord help you if any of the tables have read-only partitions.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2015 at 9:31 am
GilaMonster (9/7/2015)
Jayanth_Kurup (9/7/2015)
Find and replace will change the varchar to nvarchar but you still need to account for the increase in the column width now. I think dynamic sql might be the better option.What increase in column width? OP asked just to change varchar to nvarchar,, no mention of changing the length of the columns as well
Mea Culpa , misunderstood the question. Find replace should do the trick , Too bad there isnt a better way
September 7, 2015 at 9:33 am
Jeff Moden (9/7/2015)
Minnu (9/7/2015)
Hi Team,I have a list of 100+ tables in my SQL Database with most of the columns are declared as VARCHAR, I want to create the same 100+ tables in new environment with same structure but Varchar datatype should be NVARCHAR.
How to generate an automatic table scripts with new datatype (NVARCHAR) for all the tables.
Please suggest.
Unless all of those tables are just one part of the database, have an identical purpose, and are nearly identical in form, fit, and function, I'd like to recommend not doing this.
Other than the blob datatypes (VARCHAR(MAX) most specifically in this case), any VARCHAR defined over 4000 characters will fail. The size of your database footprint will double. Things like single character "flag" columns may unnecessarily double but will, of course, double either way. Many of your indexes may no longer fit into 900 bytes. Columns such as telephone "numbers" and SSNs will be unnecessary be doubled in byte size. In theory, all of your queries will take twice as long to run because each table page will only be able to hold half the number of rows at the very best. And, of course, since you stated that most of the columns are declared as VARCHAR, you may have a wealth of datatype sins in your database that you're not only going to perpetuate, but double the space requirements for in memory, on disk, and in your backups. Then, there's any dynamic SQL that may be involved not to mention possibly changing objects and code in managed code if any is present. I can only speculate as to what it will do to any reporting software that you're running against those tables.
If you have already planned for such things, then good enough. If not, then you need to because a whole lot of things are going to change other than just the datatypes in the columns in the tables. Lord help you if any of the tables have read-only partitions.
There really should be a like button on the forums !!! Great catch on the index widths , not something most people consider when chaning datatypes.
September 7, 2015 at 12:09 pm
Thanks, Jayanth.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2015 at 1:28 am
Thank you for suggestions.
Is there any third party tools or SQL Server tools to compare the DB's and generate automatic scripts to sync.
Is SQL Database manager will help on this...?
Please suggest
September 8, 2015 at 2:02 am
I've never used it before, but I think SQL Compare from Redgate will do that for you.
John
September 8, 2015 at 4:46 am
Minnu (9/8/2015)
Thank you for suggestions.Is there any third party tools or SQL Server tools to compare the DB's and generate automatic scripts to sync.
Is SQL Database manager will help on this...?
Please suggest
Redgate SQL Compare [/url]is the best tool for this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2015 at 5:41 am
Visual Studio (even just the shell) can do this too. Think it might need SSDT (SQL Server Data Tools) installed.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply