Sqlfanatic.files.wordpress.com



DECLARE @sqlcreate NVARCHAR(MAX) = N''; SELECT @sqlcreate +='CREATE TABLE [dbo].[Staging_'+so.name + '] (' + o.list + ')' FROM sysobjects so CROSS APPLY (SELECT ' [' + column_name + '] '+'['+ISNULL(domain_name,data_type) +']' + CASE ISNULL(domain_name,data_type) WHEN 'sql_variant' THEN '' WHEN 'text' THEN '' WHEN 'ntext' THEN '' when 'image' THEN '' WHEN 'xml' THEN '' WHEN 'Name' THEN '' --add ur userdefined dtype here WHEN 'decimal' THEN '(' + CAST(numeric_precision AS VARCHAR) + ', ' + CAST(numeric_scale AS VARCHAR) + ')' WHEN 'numeric' THEN '(' + CAST(numeric_precision AS VARCHAR) +', ' + CAST(numeric_scale AS VARCHAR) +')' ELSE Coalesce('(' + CASE WHEN character_maximum_length = -1 THEN 'MAX' ELSE CAST(character_maximum_length AS VARCHAR) END + ')', '') END + ' '+ 'NULL' +',' FROM information_schema.columns WHERE table_name = so.name ORDER BY ordinal_position FOR XML PATH('')) o (list) WHERE xtype = 'U'EXEC sp_executesql @sqlcreate ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download