create proc sp_MSget_type @tabid int, @colid int, @colname sysname output, @typestring nvarchar(4000) output as declare @ccolchar nvarchar(5) declare @coltype tinyint declare @prec smallint declare @scale int declare @ccoltype sysname declare @xtype int select @colname = c.name, @xtype = c.xtype, @prec = c.prec, @scale = c.scale, @ccoltype = t.name from syscolumns c, systypes t where c.id = @tabid and c.colid = @colid and c.xtype = t.xusertype select @typestring = @ccoltype -- datatypes requiring precision (nchar, nvarchar, binary, varbinary) -- format: @var (prec) if @ccoltype in (N'char',N'nchar', N'varchar', N'nvarchar', N'binary', N'varbinary') select @typestring = @typestring + N'(' + rtrim(convert(nchar(10),@prec)) + N')' -- datatypes requiring precision & scale (numeric & decimal) -- format: @var (prec, scale) else if @ccoltype in (N'numeric', N'decimal') select @typestring = @typestring + N'(' + rtrim(convert(nchar(10),ColumnProperty(@tabid, @colname, 'PRECISION'))) + N',' + rtrim(convert(nchar(10),@scale)) + N')' -- text/image datatypes cannot be declared or used locally else if @ccoltype in (N'text',N'ntext',N'image') select @typestring = NULL