DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'SEARCH_KEYWORD'
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
Jun 18, 2010
Search All Columns in All Tables using TSQL
Subscribe to:
Post Comments (Atom)
23 comments:
支持你!!!期待你的更新!!!相信一定會更好!!!! .................................................................
Poverty tries friends.................................................................
閒來無聊逛逛blog~~跟您打聲招呼~~.................................................................
一個人的價值,應該看他貢獻了什麼,而不是他取得了什麼............................................................
如果成為一支火柴,也要點亮一個短暫的宇宙;如果是一隻烏鴉,也要叫疼閉塞的耳膜。.................................................................
Thanks a lot for sharing (o>▽<)..................................................................
感謝不吝分享您的心得............................................................
Pen and ink is wits plough. ............................................................
人生中最好的禮物就是屬於自己的一部份..................................................
精彩的部落格 值得一推再推 支持你............................................................
與人相處不妨多用眼睛說話,多用嘴巴思考. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
人生中最好的禮物就是屬於自己的一部份............................................................
Judge not a book by its cover.. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
獨居時,要反省自己的過錯;在社會大眾之間,則要忘卻別人的過失。..................................................
你不能改變容貌~~但你可以展現笑容.................................................................
來打聲招呼-大家好!!!............................................................
時時刻刻抵抗誘惑,就是一種勝利。............................................................
人生有些波折,才能有些成長,所以不論順逆,凡是成長、成功的助緣,都應該心存感激。.................................................
成功多屬於那些很快做出決定,卻又不輕易變更的人。而失敗也經常屬於那些很難做出決定,卻又經常變更的人..................................................... ............
永遠支持你呀!!HO!!HO!!HO!!HO!!~~............................................................
心平氣和~祝你也快樂~~..................................................
感謝你的分享 要繼續發表好文章喔..................................................
在莫非定律中有項笨蛋定律:「一個組織中的笨蛋,恆大於等於三分之二。」............................................................
Post a Comment