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