Jun 18, 2010

Search All Columns in All Tables using TSQL

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

kick it on DotNetKicks.com

23 comments:

佩芳 said...

支持你!!!期待你的更新!!!相信一定會更好!!!! .................................................................

常映 said...

Poverty tries friends.................................................................

江婷 said...

閒來無聊逛逛blog~~跟您打聲招呼~~.................................................................

建霖建霖 said...

一個人的價值,應該看他貢獻了什麼,而不是他取得了什麼............................................................

姿柯瑩柯dgdd憶曾g智曾 said...

如果成為一支火柴,也要點亮一個短暫的宇宙;如果是一隻烏鴉,也要叫疼閉塞的耳膜。.................................................................

JasonBirk佳琪 said...

Thanks a lot for sharing (o>▽<)..................................................................

冠廷 said...

感謝不吝分享您的心得............................................................

許紀廷 said...

Pen and ink is wits plough. ............................................................

盈廖生家秀蔡 said...

人生中最好的禮物就是屬於自己的一部份..................................................

曾法幸 said...

精彩的部落格 值得一推再推 支持你............................................................

琬安琬安 said...

與人相處不妨多用眼睛說話,多用嘴巴思考. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

bur蔡ge佳ssal郁berto said...

人生中最好的禮物就是屬於自己的一部份............................................................

陳晏李秀樺雄 said...

Judge not a book by its cover.. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

孫邦柔 said...

獨居時,要反省自己的過錯;在社會大眾之間,則要忘卻別人的過失。..................................................

承王蓁 said...

你不能改變容貌~~但你可以展現笑容.................................................................

家唐銘 said...

來打聲招呼-大家好!!!............................................................

沈貞李添睿儀 said...

時時刻刻抵抗誘惑,就是一種勝利。............................................................

偉曹琬 said...

人生有些波折,才能有些成長,所以不論順逆,凡是成長、成功的助緣,都應該心存感激。.................................................

冠陳儒 said...

成功多屬於那些很快做出決定,卻又不輕易變更的人。而失敗也經常屬於那些很難做出決定,卻又經常變更的人.................................................... ............

張王雅竹欣虹 said...

永遠支持你呀!!HO!!HO!!HO!!HO!!~~............................................................

玉韓韓韓婷韓韓韓韓 said...

心平氣和~祝你也快樂~~..................................................

吳新順 said...

感謝你的分享 要繼續發表好文章喔..................................................

蒲亮奇 said...

在莫非定律中有項笨蛋定律:「一個組織中的笨蛋,恆大於等於三分之二。」............................................................