取得資料表結構說明

gistfile1.txt

USE [OA]
GO
/****** Object:  StoredProcedure [ERP].[usp_GetSchemaDescription]    Script Date: 2019/5/22 下午 02:07:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Louis
-- Create date: 2018/09/25
-- Description: 取得資料表結構說明
-- =============================================
ALTER PROCEDURE [ERP].[usp_GetSchemaDescription] 
-- Add the parameters for the stored procedure here
   @TableName varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @Result Table ( table_name varchar(50), column_name varchar(50), keytype varchar(50) )
Insert into @Result
SELECT KU.table_name,column_name ,TC.CONSTRAINT_TYPE as keytype
                            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                            INNER JOIN
                            INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
                              ON TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME

                    SELECT tab.name table_name,
   table_chinese_name = (SELECT VALUE
                            FROM   Fn_listextendedproperty(NULL, 'schema', 'ERP', 'table',
                                   tab.name,
                                   null,null)) ,
                           col.colid column_id,
                           col.name column_name,
                           column_chinese_name = (SELECT VALUE
                           FROM   Fn_listextendedproperty(NULL, 'schema', 'ERP', 'table',
                                   tab.name,
                                   'column',
                                           col.name)),
                           typ.name column_type,
                           -- col.prec PRECISION,
                           -- col.scale scale,
   length = iif( typ.name ='decimal', cast(col.prec as varchar(2)) + ',' +  cast(col.scale as varchar(2)),iif(typ.name = 'nvarchar', cast(col.prec as varchar(4)),cast(col.length as varchar(4)))),
                           -- col.length, 
                           com.TEXT default_value,
                           CASE
                             WHEN col.isnullable = 1 THEN 'Y'
                             ELSE 'N'
                           END is_nullable,
                           CASE
                             WHEN col.status & 0X80 = 0X80 THEN 'Y'
                             ELSE 'N'
                           END is_identity,
keytype = (Select top 1 iif(keytype = 'PRIMARY KEY','主鍵值', iif(keytype = 'FOREIGN KEY', '外來鍵',keytype)) from @Result Where table_name = tab.name and column_name = col.name)
                    FROM   sysobjects tab,
                           syscolumns col
                           LEFT OUTER JOIN syscomments com
                                           INNER JOIN sysobjects obj
                                             ON com.id = obj.id
                             ON col.cdefault = com.id
                                AND com.colid = 1, 
                           systypes typ
                    WHERE tab.id = col.id
      AND tab.name = @TableName
                           AND tab.xtype = 'U'
                           AND col.xusertype = typ.xusertype
                    order by tab.name,CONVERT(int, col.colid)

END