201102151104用關鍵字搜尋所有資料表中的所有欄位in Tsql

偶然在網路上發現一個很好用的T-Sql語法,
因為一些原因需要把Xinbei改成New Taipei,
資料庫使用sql 2008
語法如下:

DECLARE @SearchStr nvarchar(100)

SET @SearchStr = 'SearchKeyword'

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

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 ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
  )

  IF @ColumnName IS NOT NULL
  BEGIN
   INSERT INTO #Results
   EXEC
   (
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
   )
  END
 END 
END

SELECT ColumnName, ColumnValue FROM #Results

沒有上一則|日誌首頁|沒有下一則
回應
關鍵字
    沒有新回應!





Powered by Xuite
MoMo's 小水滴

我的小圈圈