SQL Server查詢資料庫表和資料庫字段

在SQL Server中查詢資料庫表和字段的方式可以有三種

#####方法一

-- 查詢所有表
SELECT * FROM sys.tables;
-- 查詢所有列
SELECT * FROM sys.columns;
-- 查詢所有擴充屬性，我們在設計資料庫表和字段時寫的中文備注等資訊會儲存在這裡
SELECT * FROM sys.extended_properties;
-- 查詢字段類型
SELECT * FROM sys.types;

-- 資料庫表與字段聯合查詢
SELECT 
	sys.tables.name AS 資料表名,
	sys.columns.name AS 字段名稱, 
	sys.types.name AS 字段類型, 
	sys.columns.max_length AS 類型長度, 
	sys.columns.is_nullable AS 是否允許為NULL, 
	(SELECT 
		value 
	 FROM sys.extended_properties 
	 WHERE sys.extended_properties.major_id = sys.columns.object_id 
		 AND sys.extended_properties.minor_id = sys.columns.column_id) AS 字段描述
FROM sys.columns, sys.tables, sys.types 
WHERE sys.columns.object_id = sys.tables.object_id 
AND sys.columns.system_type_id=sys.types.system_type_id;
           

#####方法二

-- 查詢所有表
SELECT * FROM sysobjects d where d.xtype='U' and d.name<>'dtproperties';
-- 查詢所有列
SELECT * FROM syscolumns;
-- 查詢字段類型
SELECT * FROM systypes;

-- 資料庫表與字段聯合查詢
SELECT
  表名=CASE WHEN a.colorder=1 THEN d.name ELSE '' END,
  表說明=CASE WHEN a.colorder=1   THEN isnull(f.value,'')   ELSE ''   END,
  字段序号=a.colorder,
  字段名=a.name,
  辨別=CASE WHEN COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 THEN '√' ELSE '' END,
  主鍵=CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype='PK' AND name IN(
      SELECT name FROM sysindexes WHERE indid IN(
      SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
      ))) THEN '√' ELSE '' END,
  類型=b.name,
  占用位元組數=a.length,
  長度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
  小數位數=ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
  允許空=CASE WHEN a.isnullable=1 THEN '√' ELSE '' END,
  預設值=ISNULL(e.text,''),
  字段說明=ISNULL(g.[value],'')
FROM	   syscolumns a
LEFT  JOIN systypes b 				 ON a.xusertype=b.xusertype
INNER JOIN sysobjects d 			 ON a.id=d.id and d.xtype='U' AND d.name<>'dtproperties'
LEFT  JOIN syscomments e 			 ON a.cdefault=e.id
LEFT  JOIN sys.extended_properties g ON a.id=g.major_id 		  AND a.colid=g.minor_id
LEFT  JOIN sys.extended_properties f ON d.id=f.major_id 		  AND f.minor_id=0
ORDER BY a.id,a.colorder
           

注意：syscolumns與sys.columns、systypes與sys.types并不是同一張表

方法三

在SQL Server中存在表

information_schema.tables

和

information_schema.columns

我們可以通過這兩張表來查詢需要的字段。（注意：mysql中同樣存在這兩張表，但字段會有所不同，在mysql中有

table_comment

和

column_comment

字段可以查詢到資料表描述和字段描述資訊，而SQL Server中的這兩張表中并沒有這兩個字段

）

-- 查詢所有表
SELECT * FROM information_schema.tables;
-- 查詢所有字段
SELECT * FROM information_schema.columns;

-- 資料庫表與字段聯合查詢，該方法在SQL Server無法查詢到表和字段備注
SELECT table_name AS 資料表名,
column_name AS 字段名,
ISNULL(column_default,'') AS 預設值,
is_nullable AS 是否允許為NULL,
data_type AS 資料類型,
ISNULL(ISNULL(ISNULL(character_maximum_length,numeric_precision),datetime_precision),1) AS 類型長度
FROM information_schema.columns
WHERE NOT table_name IN('sysdiagrams','dtproperties');