搜尋資料庫所有欄位


Posted by WayneCheng on 2021-01-08

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

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

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


#Database #MSSQL







Related Posts

Debounce 與 Memoize

Debounce 與 Memoize

先學完物件導向,學 this 才有意義

先學完物件導向,學 this 才有意義

排列組合 & 機率(Permutation, Combination & Probability)

排列組合 & 機率(Permutation, Combination & Probability)


Comments