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

  • 11850
  • 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'   --列出指定資料庫及資料表下的所有欄位

執行結果如下圖: