萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2000 實用技巧大全:查詢、結構、分頁、事務等

一、查詢系統表獲取元數據

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:保留重複。
copyright © 萬盛學電腦網 all rights reserved