DECLARE @table varchar(100)
SET @table = 'claims'

DECLARE @noScale varchar(255)
SET @noScale = 'INT,TINYINT,BIGINT,MONEY,SMALLMONEY'
SET @noScale=@noScale+',BIT,SMALLDATETIME,DATETIME' --Need for SQL2000
SELECT colName = ',' +
CASE
WHEN C.CHARACTERMAXIMUMLENGTH IS NOT NULL
THEN C.COLUMNNAME + ' ' + UPPER(C.DATATYPE) + '(' + CAST(C.CHARACTERMAXIMUMLENGTH AS varchar(10)) + ')'
WHEN C.NUMERICSCALE IS NULL
THEN C.COLUMNNAME + ' ' + UPPER(C.DATATYPE)
WHEN C.CHARACTERMAXIMUMLENGTH IS NULL AND CHARINDEX(C.DATATYPE, @noScale) > 0
THEN C.COLUMNNAME + ' ' + UPPER(C.DATATYPE)
WHEN C.NUMERICPRECISION > 0 AND CHARINDEX(C.DATATYPE, @noScale) = 0
THEN C.COLUMNNAME + ' ' + UPPER(C.DATATYPE)
+ '(' + CAST(C.NUMERICPRECISION AS varchar(10))
+ ',' + CAST(C.NUMERICSCALE AS varchar(10)) + ')'
END,
C.CHARACTERMAXIMUMLENGTH,
C.NUMERICPRECISION,
C.NUMERICSCALE
FROM INFORMATIONSCHEMA.COLUMNS C
WHERE C.TABLENAME = @table