I modified the "--Get the columns, their definitions and defaults" in order solve the problem of custom data type having name > 16 char, to include 'binary' data type and also the new sql2008 data type 'datetime2', 'datetimeoffset' and 'time' , doing that i changed a little bit the logic.
definitionDECLARE @DbCollationVARCHAR(255) = convert(varchar(255) ,databasepropertyex(db_name(),'collation')),
DECLARE @TYPELENINT
from and join to be modified for use in temp table --removed invalid code here which potentially selected wrong table--thanks David Grifiths @SSC!
SELECT
@STRINGLEN = MAX(LEN(QUOTENAME(COLS.[name]))) + 1, --modified
@TYPELEN = MAX(LEN(typ.[name])) + 1 -- modified
FROM sys.objects OBJS
INNER JOIN sys.columns COLS
ON OBJS.[object_id] = COLS.[object_id]
AND OBJS.[object_id] = @TABLE_ID
INNER JOIN sys.types typ
ON COLS.user_type_id=typ.user_type_id
--##############################################################################
--Get the columns, their definitions and defaults.
code usable in standard and temp table revisited moving at the end check for identity, nullable, collation and default constraint; collation are explicited only if different from default.
--Get the columns, their definitions and defaults.
--##############################################################################
SELECT
@FINALSQL = @FINALSQL
+ CASE
WHEN COLS.[is_computed] = 1
THEN @vbCrLf
+ QUOTENAME(COLS.[name])
+ ' '
+ SPACE(@STRINGLEN - LEN(QUOTENAME(COLS.[name])))
+ ' AS ' + ISNULL(CALC.definition,'')
+ CASE
WHEN CALC.is_persisted = 1
THEN ' PERSISTED'
ELSE ''
END
ELSE @vbCrLf
+ QUOTENAME(COLS.[name])
+ ' '
+ SPACE(@STRINGLEN - LEN(QUOTENAME(COLS.[name])))
+ UPPER(TYPE_NAME(COLS.[user_type_id]))
+ CASE
-- data types using precision and scaleIE NUMERIC(10,2)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('decimal','numeric')
THEN '('
+ CONVERT(VARCHAR,COLS.[precision])
+ ','
+ CONVERT(VARCHAR,COLS.[scale])
+ ') '
+ SPACE( 9 - 3 + @TYPELEN-- 3 FOR '(,)' + 3 BYTE FOR PRECISION + 3 BYTE FOR SCALE
- LEN(CONVERT(VARCHAR,COLS.[precision]))
- LEN(CONVERT(VARCHAR,COLS.[scale]))
- LEN(TYPE_NAME(COLS.[user_type_id]))
)
-- data types using scale onlyIE DATETIME2(7)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('datetime2','datetimeoffset','time')
THEN '('
+ CONVERT(VARCHAR,COLS.[scale])
+ ') '
+ SPACE( 9 - 2 + @TYPELEN-- 2 FOR '()'
- LEN(CONVERT(VARCHAR,COLS.[scale]))
- LEN(TYPE_NAME(COLS.[user_type_id]))
)
--data type using max_lengthie VARCHAR(40)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('char','varchar','binary','varbinary')
THEN CASE
WHEN COLS.[max_length] = -1
THEN '(max)'
+ SPACE( 10 + @TYPELEN
- LEN('(MAX)')
- LEN(TYPE_NAME(COLS.[user_type_id]))
)
ELSE '('
+ CONVERT(VARCHAR,COLS.[max_length])
+ ') '
+ SPACE(9-2 + @TYPELEN-- 2 FOR '()'
- LEN(CONVERT(VARCHAR,COLS.[max_length]))
- LEN(TYPE_NAME(COLS.[user_type_id]))
)
END
--data type using max_length (BUT DOUBLED) ie NVARCHAR(40)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('nchar','nvarchar')
THEN CASE
WHEN COLS.[max_length] = -1
THEN '(max)'
+ SPACE( 10 + @TYPELEN
- LEN('(MAX)')
- LEN(TYPE_NAME(COLS.[user_type_id]))
)
ELSE '('
+ CONVERT(VARCHAR,(COLS.[max_length] / 2))
+ ') '
+ SPACE( 9 - 2 + @TYPELEN
- LEN(CONVERT(VARCHAR,(COLS.[max_length] / 2)))
- LEN(TYPE_NAME(COLS.[user_type_id]))
)
END
-- all other data type defined without using parameterIE int, bigint, float, smalldatetime, bit, datetime, money, CUSTOM DATA TYPE, .....
ELSE SPACE(10 + @TYPELEN - LEN(TYPE_NAME(COLS.[user_type_id])))
END
+ CASE
WHEN COLUMNPROPERTY ( @TABLE_ID , COLS.[name] , 'IsIdentity' ) = 0
THEN ''
ELSE ' IDENTITY('
+ CONVERT(VARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )
+ ','
+ CONVERT(VARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )
+ ')'
END
--collate to comment out when not desired
+ CASE
WHEN COLS.collation_name IS NULL
or COLS.collation_name = @DbCollation -- only if column collation <> default collation
THEN ''
ELSE ' COLLATE ' + COLS.collation_name
END
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
+ CASE
WHEN COLS.[default_object_id] = 0
THEN ''
--ELSE ' DEFAULT ' + ISNULL(def.[definition] ,'')
--optional section in case NAMED default constraints are needed:
ELSE ' CONSTRAINT ' + quotename(DEF.name) + ' DEFAULT ' + ISNULL(DEF.[definition] ,'')
--i thought it needed to be handled differently! NOT!
END --CASE cdefault
END --iscomputed
+ ','
-- FROM sys.columns COLS .........
the float(53) data type never exist if i try to create a table declaring a field float ( 1 to 24) mssql will create a "real" field if i try to create a field as float or as float(25 to 53) mssql wil create a float field.
( tested on sql2008R2 and sql2014)
Maurizio