一些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' --列出指定資料庫及資料表下的所有欄位
執行結果如下圖: