SQL
專門撈取資料表結構語法
SELECT SO.ID AS [資料表編號],SO.NAME AS [資料表名稱],'' AS [資料表中文說明],
SC.COLID AS [欄位序號],SC.NAME AS [欄位名稱],'' AS [欄位中文說明],
ST.NAME AS [資料型態],SC.LENGTH AS [長度],
CASE WHEN SC.ISNULLABLE = 0 THEN '否' ELSE '是' END AS [允許NULL],
CASE WHEN ISNULL(SK.PRIMARYKEYCOLUMN,'') = '' THEN '' ELSE 'PK' END AS [主鍵值],
CASE WHEN ISNULL(SK.ORDINAL_POSITION,0) = 0 THEN '' ELSE CONVERT(CHAR(1),SK.ORDINAL_POSITION) END AS [鍵值順序],
'' AS [鍵值與索引及備註]
FROM SYSOBJECTS SO
INNER JOIN SYSCOLUMNS SC ON SO.ID=SC.ID
INNER JOIN SYSTYPES ST ON ST.XTYPE=SC.XTYPE
LEFT JOIN (
SELECT KU.TABLE_NAME,KU.COLUMN_NAME AS PRIMARYKEYCOLUMN,KU.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_TYPE='PRIMARY KEY'
AND TC.CONSTRAINT_NAME=KU.CONSTRAINT_NAME AND KU.TABLE_NAME=TC.TABLE_NAME
) AS SK ON SK.PRIMARYKEYCOLUMN=SC.NAME AND SK.TABLE_NAME=SO.NAME
WHERE (SO.TYPE='U' AND ST.NAME<>'SYSNAME')
AND SO.NAME LIKE '%'
ORDER BY SO.NAME,SC.COLID
元哥的筆記