SQL Server實用技巧集錦：密碼修改、空值判斷、重復數據查詢等

1. 修改用戶密碼

-- 修改他人密碼（需sysadmin權限）
EXEC sp_password NULL, 'newpassword', 'UserName';

-- SA賬戶自修改
EXEC sp_password NULL, 'newpassword', 'sa';

2. 查詢非空字段

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE IS_NULLABLE = 'NO' AND TABLE_NAME = 'tablename';

3. 查找包含指定列名的表

-- 已知列名
SELECT b.name AS TableName, a.name AS ColumnName
FROM syscolumns a INNER JOIN sysobjects b ON a.id = b.id
WHERE b.type = 'U' AND a.name = '目標列名';

-- 未知列名：查找在多表中出現的同名列
SELECT o.name AS TableName, s1.name AS ColumnName
FROM syscolumns s1, sysobjects o
WHERE s1.id = o.id AND o.type = 'U'
AND EXISTS (
  SELECT 1 FROM syscolumns s2 
  WHERE s1.name = s2.name AND s1.id <> s2.id
);

4. 查詢第N行數據

-- 假設id為主鍵
SELECT * FROM (
  SELECT TOP n * FROM yourtable
) aa
WHERE NOT EXISTS (
  SELECT 1 FROM (
    SELECT TOP n-1 * FROM yourtable
  ) bb WHERE aa.id = bb.id
);

-- 或使用游標
FETCH ABSOLUTE n FROM cursor_name;