use master
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ColDefinition]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp__ColDefinition]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_SizePrecScale]') and xtype in (N'FN', N'IF', N'TF'))
	drop function [dbo].[fn_SizePrecScale]
GO

CREATE function fn_SizePrecScale(
	@Type varchar(50),
	@Length int,
	@Prec int,
	@Scale int)
	RETURNS varchar(100)
as
BEGIN
	DECLARE @RC varchar(100)
	
	IF @Type in ('smalldatetime','datatime','text','image','bit','ntext','uniqueidentifier','bigint','int','smallint','tinyint','money','smallmoney')
		SET @RC = @Type
	ELSE IF @Type in('decimal','numeric')
			SET @RC = @Type + '(' + cast(@Length as varchar) + ') [' + cast(@Prec as varchar) + ',' + cast(@Scale as varchar) + ']'
	ELSE
		SET @RC = @Type + '(' + cast(@Length as varchar) + ')'
	
	RETURN(@RC)
END

GO
CREATE PROC dbo.sp_ColDefinition
	@ObjectName sysname
AS
BEGIN
	DECLARE @xtype char(2)
	
	SELECT @xtype = xtype FROM sysobjects WHERE name = @ObjectName
	
	IF @xtype not in('U','FN','V','P')
	begin
		RAISERROR ('L''objet n''existe pas dans le catalogue', 16, 1)
		RETURN
	end

	IF @xtype in ('U','V')
		SELECT [DECLARE] = '@' + C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ',',
			C.name + ',' AS [INSERT INTO],
			'@' + C.name + ',' AS [VALUES],
			C.name + ' = @' + C.name + ',' AS [SET],
			'@' + C.name + ' = ' + C.name + ',' AS [SELECT]
		FROM sysobjects O, syscolumns C, systypes T
		WHERE O.id = C.id and C.xtype = T.xtype
			and O.name = @ObjectName and T.xtype = T.xusertype
		ORDER BY C.colorder
	ELSE
		SELECT [DECLARE] = C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ','
		FROM sysobjects O, syscolumns C, systypes T
		WHERE O.id = C.id and C.xtype = T.xtype
			and O.name = @ObjectName and T.xtype = T.xusertype
		ORDER BY C.colorder
END
GO

use master
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp__ColDefinition]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp__ColDefinition]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_SizePrecScale]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_SizePrecScale]
GO

CREATE function fn_SizePrecScale(
@Type varchar(50),
@Length int,
@Prec int,
@Scale int)
returns varchar(100)
as
begin
DECLARE @RC varchar(100)

IF @Type in ('smalldatetime','datatime','text','image','bit','ntext','uniqueidentifier','bigint','int','smallint','tinyint','money','smallmoney')
SET @RC = @Type
ELSE IF @Type in('decimal','numeric')
SET @RC = @Type + '(' + cast(@Length as varchar) + ') [' + cast(@Prec as varchar) + ',' + cast(@Scale as varchar) + ']'
ELSE
SET @RC = @Type + '(' + cast(@Length as varchar) + ')'

RETURN(@RC)
end

GO
CREATE PROC dbo.sp__ColDefinition
@ObjectName sysname
AS
BEGIN
DECLARE @xtype char(2)

SELECT @xtype = xtype FROM sysobjects WHERE name = @ObjectName

IF @xtype not in('U','FN','V','P')
begin
RAISERROR ('L''objet n''existe pas dans le catalogue', 16, 1)
RETURN
end


IF @xtype in ('U','V')
SELECT
[DECLARE] = '@' + C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ',',
C.name + ',' AS [INSERT INTO],
'@' + C.name + ',' AS [VALUES],
C.name + ' = @' + C.name + ',' AS [SET],
'@' + C.name + ' = ' + C.name + ',' AS [SELECT]
from sysobjects O, syscolumns C, systypes T
where O.id = C.id and C.xtype = T.xtype
and O.name = @ObjectName and T.xtype = T.xusertype
order by C.colorder
ELSE
SELECT
[DECLARE] = C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ','
from sysobjects O, syscolumns C, systypes T
where O.id = C.id and C.xtype = T.xtype
and O.name = @ObjectName and T.xtype = T.xusertype
order by C.colorder
END

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=549&lngWId=5

sp_ColDefinition will use the metadata tables to create a table result that looks like this :

DECLARE
---------------------
@title_id varchar(6),
@title varchar(80),
@type char(12),
@pub_id char(4),
@price money,
@advance money,
@royalty int,
@ytd_sales int,
@notes varchar(200),
@pubdate datetime(8),

sp_ColDefinition or writing upsert SP in a snap

SQL