-- ***********************************************************************
-- PROCEDURE NAME	:	GetTableDefine
-- PARAMETER		:	@tableName		
-- Description		:	ƒe[ƒuƒ‹’è‹`î•ñ
-- ***********************************************************************
CREATE PROCEDURE [dbo].[GetTableDefine]
    @tableName varchar(500)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @PKData TABLE(Col varchar(500));
	DECLARE @objectId int,@indId smallint,@pkCol varchar(500),@key varchar(500),@i smallint;

	SET @objectId = object_id(@tableName);  

	--pkˆê——
	IF EXISTS(SELECT 1 FROM sysobjects WHERE parent_obj = @objectId AND xtype = 'PK')
	BEGIN  
		SELECT @indId=indid
		FROM sysindexes a,sysobjects b  
		WHERE a.id=b.parent_obj AND a.name=b.name AND b.xtype='PK' AND b.parent_obj=@objectId;
		
		INSERT INTO @PKData(Col) SELECT index_col(@tableName,@indId,1);  --PrimaryKey‚̈ê”Ԗڂ̍€–ڎ擾
		
		--‘¼‚ÌPrimaryKey‚̍€–ڎ擾
		SET @i = 2;
		SET @key = index_col(@tableName,@indId,@i);
		WHILE (@key IS NOT NULL)  
		BEGIN
			INSERT INTO @PKData(Col) SELECT @key;
			SET @i = @i + 1;  
			SET @key = index_col(@tableName,@indId,@i);
		END		
	END

	--Œ‹‰Ê
	SELECT a.name AS [Name],'[' + b.name +  ']' + 
		CASE WHEN b.xtype in(167,175,231,239) THEN '('+ case when a.prec = -1 then 'max' else CAST(a.prec  as varchar) end + ') '  
				WHEN b.xtype in(106,108) THEN '('+ CAST(a.xprec as varchar) + ','+ CAST(a.xscale as varchar)+ ') '  --¬”‚ ‚é
				ELSE ' '
		END AS DataType,
		CASE WHEN c.Col IS NULL THEN 0 ELSE 1 END AS IsPrimaryKey,
		d.value AS [Description]
	FROM syscolumns a
	INNER JOIN systypes b ON a.xtype = b.xusertype  
	LEFT JOIN @PKData c ON a.name = c.Col
	LEFT JOIN sys.extended_properties d on d.major_id = a.id and d.name = N'MS_Description' and d.[minor_id] = a.colid
	WHERE a.id = @objectId 
	ORDER BY a.colid; 

	SET NOCOUNT OFF;
END