月份: 八月 2018

發表於 程式分享

取得MSSQL Table Schema

發表於 由Grace撰寫

一、取得Table清單

--取TABLE LIST---------------------------------------------------
select a.TABLE_CATALOG as INSTANCE,
       a.TABLE_NAME as 表格名稱,
       isnull((SELECT value   
          FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, default, default)
          WHERE name='MS_Description' and objtype='TABLE'
          and objname Collate Chinese_Taiwan_Stroke_CI_AS = a.TABLE_NAME),'') as 表格說明,
       (SELECT CASE WHEN count(*) >0 THEN 'Y' ELSE 'N' END
          FROM INFORMATION_SCHEMA.COLUMNS c
         WHERE c.Table_name=a.TABLE_NAME
           AND collation_name='Chinese_Taiwan_Stroke_CS_AI'  ) as 定序,
       convert(varchar,b.create_date,111) as 建立日期,
       convert(varchar,b.modify_date,111) as 修改日期
from INFORMATION_SCHEMA.TABLES a
LEFT JOIN sys.all_objects b ON b.object_id=object_id(a.TABLE_NAME)
ORDER BY a.TABLE_NAME

擷取

二、取得Table定義

--取TABLE SCHEMA--------------------------------------------------
DECLARE @TABLE varchar(20)
DECLARE @table_id           int

SET @TABLE='ExchangeRate'

select @table_id = object_id(@TABLE)

--導出TABLE Schema
SELECT  a.Table_name                              as 表格名稱   
       ,b.COLUMN_NAME                             as 欄位名稱   
       ,b.DATA_TYPE                               as 資料型別
       ,(CASE WHEN b.CHARACTER_MAXIMUM_LENGTH IS NULL 
         THEN CAST(b.NUMERIC_PRECISION as varchar)+','+CAST(b.NUMERIC_SCALE as varchar) 
         ELSE CAST(b.CHARACTER_MAXIMUM_LENGTH as varchar) END) as 長度
       ,isnull(b.COLUMN_DEFAULT,'')               as 預設值   
       ,b.IS_NULLABLE                             as 是否允許空值
       ,(SELECT CASE WHEN COUNT(*) = 1 THEN 'PK' ELSE ' ' END 
           FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE d
          WHERE d.TABLE_NAME = a.Table_name 
            AND d.COLUMN_NAME= b.COLUMN_NAME) as PK
       ,(SELECT CASE WHEN c.collation_name='Chinese_Taiwan_Stroke_CS_AI' THEN 'Y' ELSE 'N' END
           FROM INFORMATION_SCHEMA.COLUMNS c
          WHERE c.Table_name=a.Table_name
            AND c.COLUMN_NAME=b.COLUMN_NAME) as 定序
       ,( SELECT value   
          FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, 'column', default)   
          WHERE name='MS_Description' and objtype='COLUMN'    
          and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME   
        ) as 欄位描述   
FROM INFORMATION_SCHEMA.TABLES  a   
 LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME   
WHERE TABLE_TYPE='BASE TABLE' AND a.Table_name=@TABLE
ORDER BY a.TABLE_NAME , b.ORDINAL_POSITION

--導出Primary Key
select Z.TABLE_NAME as 表格名稱,
       Z.PK_NAME as PK名稱,
       Z.COLUMN_NAME as 欄位名稱,
       Z.KEY_SEQ as 順序
from (
select
        TABLE_QUALIFIER = convert(sysname,db_name()),
        TABLE_OWNER = convert(sysname,schema_name(o.schema_id)),
        TABLE_NAME = convert(sysname,o.name),
        COLUMN_NAME = convert(sysname,c.name),
        KEY_SEQ = convert (smallint,
            case
                when c.name = index_col(@TABLE, i.index_id,  1) then 1
                when c.name = index_col(@TABLE, i.index_id,  2) then 2
                when c.name = index_col(@TABLE, i.index_id,  3) then 3
                when c.name = index_col(@TABLE, i.index_id,  4) then 4
                when c.name = index_col(@TABLE, i.index_id,  5) then 5
                when c.name = index_col(@TABLE, i.index_id,  6) then 6
                when c.name = index_col(@TABLE, i.index_id,  7) then 7
                when c.name = index_col(@TABLE, i.index_id,  8) then 8
                when c.name = index_col(@TABLE, i.index_id,  9) then 9
                when c.name = index_col(@TABLE, i.index_id, 10) then 10
                when c.name = index_col(@TABLE, i.index_id, 11) then 11
                when c.name = index_col(@TABLE, i.index_id, 12) then 12
                when c.name = index_col(@TABLE, i.index_id, 13) then 13
                when c.name = index_col(@TABLE, i.index_id, 14) then 14
                when c.name = index_col(@TABLE, i.index_id, 15) then 15
                when c.name = index_col(@TABLE, i.index_id, 16) then 16
            end),
        PK_NAME = convert(sysname,k.name)
    from
        sys.indexes i,
        sys.all_columns c,
        sys.all_objects o,
        sys.key_constraints k
    where
        o.object_id = @table_id and
        o.object_id = c.object_id and
        o.object_id = i.object_id and
        k.parent_object_id = o.object_id and 
        k.unique_index_id = i.index_id and 
        i.is_primary_key = 1 and
        (c.name = index_col (@TABLE, i.index_id,  1) or
         c.name = index_col (@TABLE, i.index_id,  2) or
         c.name = index_col (@TABLE, i.index_id,  3) or
         c.name = index_col (@TABLE, i.index_id,  4) or
         c.name = index_col (@TABLE, i.index_id,  5) or
         c.name = index_col (@TABLE, i.index_id,  6) or
         c.name = index_col (@TABLE, i.index_id,  7) or
         c.name = index_col (@TABLE, i.index_id,  8) or
         c.name = index_col (@TABLE, i.index_id,  9) or
         c.name = index_col (@TABLE, i.index_id, 10) or
         c.name = index_col (@TABLE, i.index_id, 11) or
         c.name = index_col (@TABLE, i.index_id, 12) or
         c.name = index_col (@TABLE, i.index_id, 13) or
         c.name = index_col (@TABLE, i.index_id, 14) or
         c.name = index_col (@TABLE, i.index_id, 15) or
         c.name = index_col (@TABLE, i.index_id, 16))             
) Z
order by TABLE_NAME, KEY_SEQ

SELECT Table_name, COLUMN_NAME,
       CASE WHEN collation_name='Chinese_Taiwan_Stroke_CS_AI' THEN 'Y' ELSE 'N' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE collation_name='Chinese_Taiwan_Stroke_CS_AI'

擷取.PNG