· 7 years ago · Jan 28, 2019, 08:46 PM
1CREATE PROC SearchAllTables
2(
3@SearchStr nvarchar(100)
4)
5AS
6BEGIN
7
8-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
9-- Purpose: To search all columns of all tables for a given search string
10-- Written by: Narayana Vyas Kondreddi
11-- Site: http://vyaskn.tripod.com
12-- Tested on: SQL Server 7.0 and SQL Server 2000
13-- Date modified: 28th July 2002 22:50 GMT
14
15
16CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
17
18SET NOCOUNT ON
19
20DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
21SET @TableName = ''
22SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
23
24WHILE @TableName IS NOT NULL
25BEGIN
26 SET @ColumnName = ''
27 SET @TableName =
28 (
29 SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
30 FROM INFORMATION_SCHEMA.TABLES
31 WHERE TABLE_TYPE = 'BASE TABLE'
32 AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
33 AND OBJECTPROPERTY(
34 OBJECT_ID(
35 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
36 ), 'IsMSShipped'
37 ) = 0
38 )
39
40 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
41 BEGIN
42 SET @ColumnName =
43 (
44 SELECT MIN(QUOTENAME(COLUMN_NAME))
45 FROM INFORMATION_SCHEMA.COLUMNS
46 WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
47 AND TABLE_NAME = PARSENAME(@TableName, 1)
48 AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
49 AND QUOTENAME(COLUMN_NAME) > @ColumnName
50 )
51
52 IF @ColumnName IS NOT NULL
53 BEGIN
54 INSERT INTO #Results
55 EXEC
56 (
57 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
58 FROM ' + @TableName + ' (NOLOCK) ' +
59 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
60 )
61 END
62 END
63END
64
65SELECT ColumnName, ColumnValue FROM #Results
66 END
67
68CREATE PROC SearchAllTables
69(
70@SearchStr nvarchar(100)
71)
72AS
73BEGIN
74
75-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
76-- Purpose: To search all columns of all tables for a given search string
77-- Written by: Narayana Vyas Kondreddi
78-- Site: http://vyaskn.tripod.com
79-- Tested on: SQL Server 7.0 and SQL Server 2000
80-- Date modified: 28th July 2002 22:50 GMT
81
82-- Copyright @ 2012 Gyula Kulifai. All rights reserved.
83-- Extended By: Gyula Kulifai
84-- Purpose: To put key values, to exactly determine the position of search
85-- Resources: Anatoly Lubarsky
86-- Date extension: 19th October 2012 12:24 GMT
87-- Tested on: SQL Server 10.0.5500 (SQL Server 2008 SP3)
88
89CREATE TABLE #Results (TableName nvarchar(370), KeyValues nvarchar(3630), ColumnName nvarchar(370), ColumnValue nvarchar(3630))
90
91SET NOCOUNT ON
92
93DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
94 ,@TableShortName nvarchar(256)
95 ,@TableKeys nvarchar(512)
96 ,@SQL nvarchar(3830)
97
98SET @TableName = ''
99SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
100
101WHILE @TableName IS NOT NULL
102BEGIN
103 SET @ColumnName = ''
104
105 -- Scan Tables
106 SET @TableName =
107 (
108 SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
109 FROM INFORMATION_SCHEMA.TABLES
110 WHERE TABLE_TYPE = 'BASE TABLE'
111 AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
112 AND OBJECTPROPERTY(
113 OBJECT_ID(
114 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
115 ), 'IsMSShipped'
116 ) = 0
117 )
118 Set @TableShortName=PARSENAME(@TableName, 1)
119 -- print @TableName + ';' + @TableShortName +'!' -- *** DEBUG LINE ***
120
121 -- LOOK Key Fields, Set Key Columns
122 SET @TableKeys=''
123 SELECT @TableKeys = @TableKeys + '''' + QUOTENAME([name]) + ': '' + CONVERT(nvarchar(250),' + [name] + ') + ''' + ',' + ''' + '
124 FROM syscolumns
125 WHERE [id] IN (
126 SELECT [id]
127 FROM sysobjects
128 WHERE [name] = @TableShortName)
129 AND colid IN (
130 SELECT SIK.colid
131 FROM sysindexkeys SIK
132 JOIN sysobjects SO ON
133 SIK.[id] = SO.[id]
134 WHERE
135 SIK.indid = 1
136 AND SO.[name] = @TableShortName)
137 If @TableKeys<>''
138 SET @TableKeys=SUBSTRING(@TableKeys,1,Len(@TableKeys)-8)
139 -- Print @TableName + ';' + @TableKeys + '!' -- *** DEBUG LINE ***
140
141 -- Search in Columns
142 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
143 BEGIN
144 SET @ColumnName =
145 (
146 SELECT MIN(QUOTENAME(COLUMN_NAME))
147 FROM INFORMATION_SCHEMA.COLUMNS
148 WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
149 AND TABLE_NAME = PARSENAME(@TableName, 1)
150 AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
151 AND QUOTENAME(COLUMN_NAME) > @ColumnName
152 ) -- Set ColumnName
153
154 IF @ColumnName IS NOT NULL
155 BEGIN
156 SET @SQL='
157 SELECT
158 ''' + @TableName + '''
159 ,'+@TableKeys+'
160 ,''' + @ColumnName + '''
161 ,LEFT(' + @ColumnName + ', 3630)
162 FROM ' + @TableName + ' (NOLOCK) ' +
163 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
164 --Print @SQL -- *** DEBUG LINE ***
165 INSERT INTO #Results
166 Exec (@SQL)
167 END -- IF ColumnName
168 END -- While Table and Column
169END --While Table
170
171SELECT TableName, KeyValues, ColumnName, ColumnValue FROM #Results
172END
173
174/* Reto Egeter, fullparam.wordpress.com */
175
176DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
177SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
178SET @FullRowResult = 1
179SET @FullRowResultRows = 3
180SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
181SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
182SET @SearchStrInXML = 0 /* Searching XML data may be slow */
183
184IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
185CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))
186
187SET NOCOUNT ON
188
189DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
190SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
191DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))
192
193WHILE @TableName IS NOT NULL
194BEGIN
195SET @TableName =
196(
197SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
198FROM INFORMATION_SCHEMA.TABLES
199WHERE TABLE_TYPE = 'BASE TABLE'
200AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
201AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
202AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
203)
204IF @TableName IS NOT NULL
205BEGIN
206DECLARE @sql VARCHAR(MAX)
207SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
208FROM INFORMATION_SCHEMA.COLUMNS
209WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
210AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
211AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
212AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)'
213INSERT INTO @ColumnNameTable
214EXEC (@sql)
215WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
216BEGIN
217PRINT @ColumnName
218SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
219SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),'''
220WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
221ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
222FROM ' + @TableName + ' (NOLOCK) ' +
223' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
224WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
225ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
226INSERT INTO #Results
227EXEC(@sql)
228IF @@ROWCOUNT > 0 IF @FullRowResult = 1
229BEGIN
230SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
231' FROM ' + @TableName + ' (NOLOCK) ' +
232' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
233WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
234ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
235EXEC(@sql)
236END
237DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
238END
239END
240END
241SET NOCOUNT OFF
242
243SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
244GROUP BY TableName, ColumnName, ColumnValue, ColumnType
245
246CREATE PROC [dbo].[SearchAllTables_Like]
247(
248 @SearchStr nvarchar(100)
249)
250AS
251BEGIN
252
253-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
254-- Purpose: To search all columns of all tables for a given search string
255-- Written by: Narayana Vyas Kondreddi
256-- Site: http://vyaskn.tripod.com
257-- Tested on: SQL Server 7.0 and SQL Server 2000
258-- Date modified: 28th July 2002 22:50 GMT
259
260
261CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
262
263SET NOCOUNT ON
264
265DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
266SET @TableName = ''
267SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
268
269WHILE @TableName IS NOT NULL
270BEGIN
271 SET @ColumnName = ''
272 SET @TableName =
273 (
274 SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
275 FROM INFORMATION_SCHEMA.TABLES
276 WHERE TABLE_TYPE = 'BASE TABLE'
277 AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
278 AND OBJECTPROPERTY(
279 OBJECT_ID(
280 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
281 ), 'IsMSShipped'
282 ) = 0
283 )
284
285 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
286 BEGIN
287
288 SET @ColumnName =
289 (
290 SELECT MIN(QUOTENAME(COLUMN_NAME))
291 FROM INFORMATION_SCHEMA.COLUMNS
292 WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
293 AND TABLE_NAME = PARSENAME(@TableName, 1)
294 AND DATA_TYPE IN ('guid', 'int', 'char', 'varchar', 'nchar', 'nvarchar')
295 AND QUOTENAME(COLUMN_NAME) > @ColumnName
296 )
297
298 IF @ColumnName IS NOT NULL
299 BEGIN
300 INSERT INTO #Results
301 EXEC
302 (
303 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
304 FROM ' + @TableName + ' (NOLOCK) ' +
305 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
306 )
307 END
308 END
309END
310
311SELECT ColumnName, ColumnValue FROM #Results
312END
313
314use name_of_database
315
316EXEC spUtil_SearchText 'value_searched', 0, 0