利用SQL語法抓取資料庫的結構

參考引用
--
–方法一：取得所有使用者Table及欄位(型態，長度)

SELECT Case when syscolumns.colid='1' then sysobjects.name else '' end as 資料表名稱,
    syscolumns.name as 欄位名稱,systypes.name+CASE
    when systypes.name in('number','decimal')
    then '('+convert(varchar,syscolumns.prec)+','+convert(varchar,syscolumns.scale)+')'
    when systypes.name like '%char' then '('+convert(varchar,syscolumns.prec)+')'
    else '' end as 欄位型態,
    IsNull(syscomments.text,'') as 預設值,case isnullable when 1 then 'Y' else 'N' end as 允許NULL
FROM sysobjects
LEFT JOIN syscolumns ON(syscolumns.id=sysobjects.id)
LEFT JOIN systypes ON(syscolumns.xtype=systypes.xtype)
LEFT JOIN syscomments on(syscolumns.cdefault=syscomments.id)
WHERE sysobjects.xtype='U' and sysobjects.name<>'dtproperties' and systypes.name<>'sysname'
ORDER BY  sysobjects.name,syscolumns.colorder
–方法二：取得所有使用者Table及欄位(型態,長度,PK)

SELECT CASE A.ORDINAL_POSITION when 1 then A.TABLE_NAME else '' end as 資料表名稱,
    A.ColUMN_NAME as 欄位名稱,DATA_TYPE+CASE
        WHEN DATA_TYPE like '%char' THEN '('+Convert(Varchar,CHARACTER_MAXIMUM_LENGTH)+')'
        WHEN DATA_TYPE='numeric' or DATA_TYPE='decimal'
        THEN  '('+Convert(Varchar,NUMERIC_PRECISION)+','+Convert(Varchar,NUMERIC_SCALE)+')'
        ELSE '' END AS 欄位型態,
    IsNull(COLUMN_DEFAULT,'') AS 預設值,IS_NULLABLE as 允許NULL,IsNull(COLLATION_NAME,'') AS 定序,
    IsNull(CONSTRAINT_NAME,'') AS 主鍵名稱
FROM INFORMATION_SCHEMA.COLUMNS as A
LEFT JOIN ( --抓PK資料
    SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    INNER JOIN sysobjects ON(CONSTRAINT_NAME=sysobjects.name and xtype='PK')
    WHERE TABLE_NAME<>'dtproperties'
) AS B ON(A.TABLE_NAME=B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME)
Order by A.TABLE_NAME,A.ORDINAL_POSITION

--抓關連(Table)
SELECT case when keyno=1 then cons.name else '' end as key_name,
case when keyno=1 then fk.name else '..' end as main_table,fkcol.name as main_column,
case when keyno=1 then rk.name else '..' end as ref_table,rkcol.name as ref_column
FROM sysforeignkeys
INNER JOIN sysobjects as cons ON(sysforeignkeys.constid=cons.id)
INNER JOIN sysobjects as fk  ON(sysforeignkeys.fkeyid=fk.id)
INNER JOIN sysobjects as rk ON(sysforeignkeys.rkeyid=rk.id)
INNER JOIN syscolumns as fkcol on(sysforeignkeys.fkey=fkcol.colid and sysforeignkeys.fkeyid=fkcol.id )
INNER JOIN syscolumns as rkcol on(sysforeignkeys.rkey=rkcol.colid and sysforeignkeys.rkeyid=rkcol.id)
Order By fk.name,rk.name,keyno