# 搜尋資料庫所有欄位

###### tags: `Database`

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

> ### 1. 前言 (~~廢話~~)
> ### 2. 如何搜尋？
> ### 3. 使用建議

---
## 測試環境：
>MS SQL：SQL Server 2019 Linux
>
>SSMS：Microsoft SQL Server Management Studio 18

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

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

因為文字搜尋較難鎖定特定資料，所以將[Will 保哥](https://blog.miniasp.com/)的版本稍作修改為時間的版本，順利解決了此次的問題。

---
## 2. 如何搜尋？
搜尋的內容分為
- [Will 保哥](https://blog.miniasp.com/)修改的**純文字搜尋**版本
- 與本篇所要提供的**時間(DateTime)搜尋**版本

關於**純文字搜尋**的部分還請移駕至 [Will 保哥的 Blog](https://blog.miniasp.com/post/2010/07/12/Search-all-columns-of-all-tables-in-a-database-for-a-keyword) 文章中間的 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](https://docs.microsoft.com/zh-tw/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver15)

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 保哥](https://blog.miniasp.com/post/2010/07/12/Search-all-columns-of-all-tables-in-a-database-for-a-keyword)
2. [MSDN](https://docs.microsoft.com/zh-tw/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver15)
---

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