-- ***********************************************************************
|
-- 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
|
