搜尋資料庫所有欄位

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

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

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