一、查詢系統表獲取元數據
1. 查詢表名
SELECT * FROM sysobjects WHERE name LIKE '%user%'
2. 已知列名查表
SELECT * FROM sysobjects s
WHERE EXISTS (
SELECT * FROM syscolumns WHERE ID = s.ID AND name LIKE '%user%'
)
3. 常用元數據查詢
-- 所有用戶表
SELECT name FROM sysobjects WHERE type='U'
-- 表的所有列
SELECT name FROM syscolumns WHERE ID=OBJECT_ID('表名')
-- 信息模式視圖 (推薦)
SELECT * FROM information_schema.tables
SELECT * FROM information_schema.views
SELECT * FROM information_schema.columns
4. Access 系統表
SELECT name FROM MSysObjects WHERE type=1 AND flags=0
-- 需在選項中勾選「顯示系統對象」
二、表結構詳細查詢
1. 基本字段屬性
SELECT syscolumns.name, systypes.name, syscolumns.isnullable, syscolumns.length
FROM syscolumns, systypes
WHERE syscolumns.xusertype = systypes.xusertype
AND syscolumns.id = OBJECT_ID('你的表名')
2. 含主鍵、標識列、描述的完整結構
DECLARE @table_name VARCHAR(MAX) = '你的表名'
SELECT sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable,
(SELECT COUNT(*) FROM sys.identity_columns
WHERE object_id = sys.columns.object_id AND column_id = sys.columns.column_id) AS is_identity,
(SELECT value FROM sys.extended_properties
WHERE major_id = sys.columns.object_id AND minor_id = sys.columns.column_id) AS description
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
AND sys.tables.name = @table_name
ORDER BY sys.columns.column_id
3. 單獨查詢標識列
SELECT [name] FROM syscolumns
WHERE id=OBJECT_ID(N'你的表名') AND COLUMNPROPERTY(id,name,'IsIdentity')=1
4. 主外鍵約束
EXEC sp_helpconstraint '你的表名'
5. 極完整的表結構查詢(含默認值、說明等)
SELECT
表名 = CASE a.colorder WHEN 1 THEN c.name ELSE '' END,
序 = a.colorder,
字段名 = a.name,
標識 = CASE COLUMNPROPERTY(a.id,a.name,'IsIdentity') WHEN 1 THEN '√' ELSE '' END,
主鍵 = CASE WHEN EXISTS(...) THEN '√' ELSE '' END,
類型 = b.name,
字節數 = a.length,
長度 = COLUMNPROPERTY(a.id,a.name,'Precision'),
小數 = CASE ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) WHEN 0 THEN '' ELSE CAST(COLUMNPROPERTY(a.id,a.name,'Scale') AS VARCHAR) END,
允許空 = CASE a.isnullable WHEN 1 THEN '√' ELSE '' END,
默認值 = ISNULL(d.[text],''),
說明 = ISNULL(e.[value],'')
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype=b.xusertype
INNER JOIN sysobjects c ON a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties'
LEFT JOIN syscomments d ON a.cdefault=d.id
LEFT JOIN sys.extended_properties e ON a.id=e.class AND a.colid=e.minor_id
ORDER BY c.name, a.colorder
三、跨庫查詢與管理
6. 所有庫名
SELECT * FROM master..sysdatabases
7. 所有庫的所有表(游標循環)
USE master
DECLARE @db_name VARCHAR(100), @sql VARCHAR(200)
DECLARE cur_tables CURSOR FOR SELECT name FROM sysdatabases
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @db_name
SET @sql = 'SELECT * FROM ' + @db_name + '.dbo.sysobjects WHERE xtype=''U'''
EXEC (@sql)
FETCH NEXT FROM cur_tables INTO @db_name
END
CLOSE cur_tables
DEALLOCATE cur_tables
四、實用業務技巧
1. 按姓氏筆畫排序
SELECT * FROM TableName ORDER BY CustomerName COLLATE Chinese_PRC_Stroke_CI_AS
2. 分頁查詢 (SQL 2005+)
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY tab.ID DESC) AS rownum, tab.*
FROM 表名 AS tab
) AS t WHERE rownum BETWEEN 起始位置 AND 結束位置
3. 當前庫所有用戶表
SELECT * FROM sysobjects WHERE xtype='U' AND category=0
4. 表的所有字段
SELECT name FROM syscolumns WHERE id=OBJECT_ID('表名')
5. 查看與表相關的視圖/存儲過程/函數
SELECT a.* FROM sysobjects a, syscomments b
WHERE a.id = b.id AND b.text LIKE '%表名%'
6. 所有存儲過程
SELECT name AS 存儲過程名稱 FROM sysobjects WHERE xtype='P'
7. 用戶創建的數據庫 (非 sa)
SELECT * FROM master..sysdatabases D
WHERE sid NOT IN (SELECT sid FROM master..syslogins WHERE name='sa')
-- 或
SELECT dbid, name AS DB_NAME FROM master..sysdatabases WHERE sid <> 0x01
8. 表的字段與數據類型
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '表名'
五、事務處理模板
BEGIN TRAN
INSERT INTO TableName VALUES(...)
-- 操作異常
ROLLBACK TRAN
-- 操作正常
COMMIT TRAN
六、全文匹配查詢
字段名 LIKE N'%[^a-zA-Z0-9]China[^a-zA-Z0-9]%'
OR 字段名 LIKE N'%[^a-zA-Z0-9]China'
OR 字段名 LIKE N'China[^a-zA-Z0-9]%'
OR 字段名 LIKE N'China'
七、計算查詢執行時間
DECLARE @d DATETIME
SET @d = GETDATE()
SELECT * FROM SYS_ColumnProperties
SELECT [語句執行花費時間(毫秒)] = DATEDIFF(ms, @d, GETDATE())
八、集合運算符
- UNION:合併結果並去重;UNION ALL:不去重。
- EXCEPT:差集(在 TABLE1 不在 TABLE2);EXCEPT ALL:保留重複。
- INTERSECT:交集;INTERSECT ALL:保留重複。