[SQL]列出特定資料庫內資料表結構的常用方法

2019-08-29

11871
0
SQL
2019-09-05

一些SQL常用的Table Schema顯示方式，本文以SSMS中的master資料庫為例。

在使用一些系統資料表，可能會使用到object_id來做join動作，可以使用OBJECT_ID()，來產生特定物件的識別碼，如：(參考：http://blog.benhall.me.uk/2008/03/what-is-object_id-in-sql-server/)

select OBJECT_ID('spt_fallback_db') as objectId --產生object_id

產生資料表清單：(xtype值可參考：https://blog.csdn.net/zengcong2013/article/details/68059746)

select * from sysobjects where xtype = 'U' --列出所有物件裡，屬於Table類型的物件

執行結果：

產生資料庫清單

select * from sys.databases  --列出所有的資料庫

執行結果：

僅使用sys.tables、syscolumns、systypes，來顯示Table Schema結果集，可使用下面的方式

select 
	DB_NAME() as DB, 
	tab.name as TABLE_NAME, 
	col.name as COLUMN_NAME, 
	tpe.name as DATA_TYPE,  
	col.length as CHARACTER_MAXIMUM_LENGTH,
    (case col.isnullable when '1' then 'YES' when '0' then 'NO' end) as IS_NULLABLE
from sys.tables tab 
left join syscolumns col 
on tab.object_id = col.id
left join systypes tpe 
on col.xusertype = tpe.xusertype 
where col.id = OBJECT_ID('spt_fallback_db')
order by tab.name

執行結果：

但其實可以使用更簡單的方式做顯示，可直接使用INFORMATION_SCHEMA.COLUMNS，如下圖。

select 
	TABLE_CATALOG, 
	TABLE_NAME, 
	COLUMN_NAME, 
	DATA_TYPE, 
	CHARACTER_MAXIMUM_LENGTH,
	IS_NULLABLE 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = 'spt_fallback_db'
order by TABLE_NAME

※補充：INFORMATION_SCHEMA為table_owner，INFORMATION_SCHEMA.COLUMNS為一個View Table物件，如下圖查詢可得知。

或是使用內建的Stored Procedure，如下：(參考來源https://www.cnblogs.com/fuhongwei041/archive/2008/07/29/1255957.html)

EXEC sp_helpdb  --列出所有資料庫

EXEC sp_tables @table_qualifier = 'master', @table_type = '''TABLE'''   --列出特定資料庫下的資料表，table_qualifier為資料庫名稱

EXEC sp_columns @table_qualifier = 'master', @table_name = 'spt_fallback_db'   --列出指定資料庫及資料表下的所有欄位

執行結果如下圖：

回首頁