搜尋資料庫所有欄位

tags: Database

關於 搜尋資料庫所有欄位 本篇將討論以下幾個問題

1. 前言 (廢話)

2. 如何搜尋？

3. 使用建議

---

測試環境：

MS SQL：SQL Server 2019 Linux

SSMS：Microsoft SQL Server Management Studio 18

---

1. 前言

前不久接到了一個需求，客戶希望能將 OO 產品的部分資訊由 OO 產品資料庫中撈出並同步到自家系統資料庫中，但某產品資料庫資料表多達上百張 Table，欄位命名無規則可循。

由於 OO 產品功能頁面上有部分資訊可以參照，所以就開始了手動 + 肉眼的土法煉鋼大法，大概花了五分鐘發現此法不可行，於是開始尋找可行的解決方案，所幸很快地找到了Will 保哥的文章。

因為文字搜尋較難鎖定特定資料，所以將Will 保哥的版本稍作修改為時間的版本，順利解決了此次的問題。

---

2. 如何搜尋？

搜尋的內容分為

• Will 保哥修改的純文字搜尋版本
• 與本篇所要提供的時間(DateTime)搜尋版本

關於純文字搜尋的部分還請移駕至 Will 保哥的 Blog 文章中間的 SQL 語法直接 Copy 即可使用

而本篇提供修改時間(DateTime)搜尋的版本

-- 要搜尋的日期放這邊
DECLARE @SearchDate datetime2 = '2020-02-02 20:20:20' -- <== 要搜尋的日期放這邊
-- 表示要搜尋 1 秒內
DECLARE @SearchDateEnd datetime = DATEADD(second,1,@SearchDate)
-- 換成 minute 為 1 分鐘內
-- DATEADD(minute,1,@SearchDate)
-- 換成 minute 為 1 小時內
-- DATEADD(hour,1,@SearchDate)

-- 以下無須修改
-- 以下無須修改
-- 以下無須修改
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630), ColumnDateValue datetime2)
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128)
SET @TableName = ''
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('date','datetime','datetime2')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'
SELECT ''' + @TableName + '.' + @ColumnName + ''',
LEFT(' + @ColumnName + ', 3630), '
+ @ColumnName
+ '
FROM ' + @TableName + ' (NOLOCK) '
)
END
END
END
SELECT @SearchDate AS '起始時間' ,@SearchDateEnd AS '結束時間'
SELECT * FROM #Results
WHERE ColumnDateValue BETWEEN @SearchDate AND @SearchDateEnd
DROP TABLE #Results

說明：
將要搜尋的日期填入 (e.g. 2020-02-02 20:20:20)

DECLARE @SearchDate datetime2 = '要搜尋的日期放這邊'

依據要搜尋的範圍大小填寫，大至 year 小至 nanosecond，更多請參考 MSDN

DECLARE @SearchDateEnd datetime = DATEADD(要搜尋的範圍放這邊,1,@SearchDate)
-- 表示要搜尋 1 秒內
-- DATEADD(second,1,@SearchDate)
-- 換成 minute 為 1 分鐘內
-- DATEADD(minute,1,@SearchDate)
-- 換成 minute 為 1 小時內
-- DATEADD(hour,1,@SearchDate)

---

3. 使用建議

由於是整個資料庫搜尋，對於資料庫的效能負擔極大，請千萬別隨意在 Production 上使用

若是情況允許，建議可以將資料庫備份後在本機執行

---

總結

總覺得這個需求很常見，但意外得很少討論，大多數都是尋找全部資料表名稱或是欄位名稱，希望能幫助到有需要的朋友。

---

參考資料

1. Will 保哥
2. MSDN

---

新手上路，若有錯誤還請告知，謝謝