· 8 years ago · Mar 08, 2018, 10:52 AM
1CREATE PROCEDURE dbo.sp_SearchTables
2 @Tablenames VARCHAR(500)
3,@SearchStr NVARCHAR(60)
4,@TypeOfSearch NVARCHAR(60)
5,@GenerateSQLOnly Bit = 0
6AS
7
8/*
9 Parameters and usage
10
11 @Tablenames -- Provide a single table name or multiple table name with comma seperated.
12 If left blank , it will check for all the tables in the database
13 Provide wild card tables names with comma seperated
14 EX :'%tbl%,Dim%' -- This will search the table having names comtains "tbl" and starts with "Dim"
15
16 @SearchStr -- Provide the search string. Use the '%' to coin the search. Also can provide multiple search with comma seperated
17 EX : X%--- will give data staring with X
18 %X--- will give data ending with X
19 %X%--- will give data containig X
20 %X%,Y%--- will give data containig X or starting with Y
21 %X%,%,,% -- Use a double comma to search comma in the data
22 @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.
23 By default it is 0 and it will search.
24 @TypeOfSearch -- Restricts the search by the type of column
25 "ALL" - All text types & uniqueidentifier
26 "TEXT" - Only text types
27 "GUID" - Only uniqueidentifier type
28
29 Samples :
30
31 1. To search data in a table
32
33 EXEC SP_SearchTables @Tablenames = 'T1'
34 ,@SearchStr = '%TEST%'
35
36 The above sample searches in table T1 with string containing TEST.
37
38 2. To search in a multiple table
39
40 EXEC SP_SearchTables @Tablenames = 'T2'
41 ,@SearchStr = '%TEST%'
42
43 The above sample searches in tables T1 & T2 with string containing TEST.
44
45 3. To search in a all table
46
47 EXEC SP_SearchTables @Tablenames = '%'
48 ,@SearchStr = '%TEST%'
49
50 The above sample searches in all table with string containing TEST.
51
52 4. Generate the SQL for the Select statements
53
54 EXEC SP_SearchTables @Tablenames = 'T1'
55 ,@SearchStr = '%TEST%'
56 ,@GenerateSQLOnly = 1
57
58 5. To Search in tables with specfic name
59
60 EXEC SP_SearchTables @Tablenames = '%T1%'
61 ,@SearchStr = '%TEST%'
62 ,@GenerateSQLOnly = 0
63
64 6. To Search in multiple tables with specfic names
65
66 EXEC SP_SearchTables @Tablenames = '%T1%,Dim%'
67 ,@SearchStr = '%TEST%'
68 ,@GenerateSQLOnly = 0
69
70 7. To specify multiple search strings
71
72 EXEC SP_SearchTables @Tablenames = '%T1%,Dim%'
73 ,@SearchStr = '%TEST%,TEST1%,%TEST2'
74 ,@GenerateSQLOnly = 0
75
76
77 8. To search comma itself in the tables use double comma ",,"
78
79 EXEC SP_SearchTables @Tablenames = '%T1%,Dim%'
80 ,@SearchStr = '%,,%'
81 ,@GenerateSQLOnly = 0
82
83 EXEC SP_SearchTables @Tablenames = '%T1%,Dim%'
84 ,@SearchStr = '%with,,comma%'
85 ,@GenerateSQLOnly = 0
86*/
87
88 SET NOCOUNT ON
89
90 DECLARE @SearchTypes TABLE (TypeName VARCHAR(20))
91
92 IF @TypeOfSearch = 'ALL'
93 INSERT @SearchTypes (TypeName) VALUES ('varchar'),('char'),('nvarchar'),('nchar'),('text'),('uniqueidentifier');
94 ELSE IF @TypeOfSearch = 'TEXT'
95 INSERT @SearchTypes (TypeName) VALUES ('varchar'),('char'),('nvarchar'),('nchar'),('text');
96 ELSE IF @TypeOfSearch = 'GUID'
97 INSERT @SearchTypes (TypeName) VALUES ('uniqueidentifier');
98
99 DECLARE @MatchFound BIT
100
101 SELECT @MatchFound = 0
102
103 DECLARE @CheckTableNames Table
104 (
105 Tablename sysname
106 )
107
108 DECLARE @SearchStringTbl TABLE
109 (
110 SearchString VARCHAR(500)
111 )
112
113 DECLARE @SQLTbl TABLE
114 (
115 Tablename SYSNAME
116 ,WHEREClause VARCHAR(MAX)
117 ,SQLStatement VARCHAR(MAX)
118 ,Execstatus BIT
119 )
120
121 DECLARE @SQL VARCHAR(MAX)
122 DECLARE @TblSQL VARCHAR(MAX)
123 DECLARE @tmpTblname sysname
124 DECLARE @ErrMsg VARCHAR(100)
125
126 IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%')
127 BEGIN
128
129 INSERT INTO @CheckTableNames
130 SELECT Name
131 FROM sys.tables
132 END
133 ELSE
134 BEGIN
135
136 IF CHARINDEX(',',@Tablenames) > 0
137 SELECT @SQL = 'SELECT ''' + REPLACE(@Tablenames,',','''as TblName UNION SELECT ''') + ''''
138 ELSE
139 SELECT @SQL = 'SELECT ''' + @Tablenames + ''' as TblName '
140
141 SELECT @TblSQL = 'SELECT T.NAME
142 FROM SYS.TABLES T
143 JOIN (' + @SQL + ') tblsrc
144 ON T.name LIKE tblsrc.tblname '
145
146
147
148
149 INSERT INTO @CheckTableNames
150 EXEC(@TblSQL)
151
152 END
153
154 IF NOT EXISTS(SELECT 1 FROM @CheckTableNames)
155 BEGIN
156
157 SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter'
158 PRINT @ErrMsg
159 RETURN
160
161 END
162
163
164 IF LTRIM(RTRIM(@SearchStr)) =''
165 BEGIN
166
167 SELECT @ErrMsg = 'Please specify the search string in @SearchStr Parameter'
168 PRINT @ErrMsg
169 RETURN
170 END
171 ELSE
172 BEGIN
173 SELECT @SearchStr = REPLACE(@SearchStr,',,,',',#DOUBLECOMMA#')
174 SELECT @SearchStr = REPLACE(@SearchStr,',,','#DOUBLECOMMA#')
175
176 SELECT @SQL = 'SELECT ''' + REPLACE(@SearchStr,',','''as SearchString UNION SELECT ''') + ''''
177
178 INSERT INTO @SearchStringTbl
179 (SearchString)
180 EXEC(@SQL)
181
182 UPDATE @SearchStringTbl
183 SET SearchString = REPLACE(SearchString ,'#DOUBLECOMMA#',',')
184 END
185
186 INSERT INTO @SQLTbl
187 ( Tablename,WHEREClause)
188 SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME),
189 (
190 SELECT '[' + SC.Name + ']' + ' LIKE ''' + SearchSTR.SearchString + ''' OR ' + CHAR(10)
191 FROM SYS.columns SC
192 JOIN SYS.types STy
193 ON STy.system_type_id = SC.system_type_id
194 AND STy.user_type_id =SC.user_type_id
195 CROSS JOIN @SearchStringTbl SearchSTR
196 WHERE STY.name in (SELECT * FROM @SearchTypes)
197 AND SC.object_id = ST.object_id
198 ORDER BY SC.name
199 FOR XML PATH('')
200 )
201 FROM SYS.tables ST
202 JOIN @CheckTableNames chktbls
203 ON chktbls.Tablename = ST.name
204 JOIN SYS.schemas SCh
205 ON ST.schema_id = SCh.schema_id
206 WHERE ST.name <> 'SearchTMP'
207 GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME) ;
208
209
210 UPDATE @SQLTbl
211 SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)
212
213
214
215 DELETE FROM @SQLTbl
216 WHERE WHEREClause IS NULL
217
218 WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0)
219 BEGIN
220
221 SELECT TOP 1 @tmpTblname = Tablename , @SQL = SQLStatement
222 FROM @SQLTbl
223 WHERE ISNULL(Execstatus ,0) = 0
224
225 IF @GenerateSQLOnly = 0
226 BEGIN
227
228 IF OBJECT_ID('SearchTMP','U') IS NOT NULL
229 DROP TABLE SearchTMP
230
231 EXEC (@SQL)
232
233 IF EXISTS(SELECT 1 FROM SearchTMP)
234 BEGIN
235 SELECT Tablename=@tmpTblname,* FROM SearchTMP
236 SELECT @MatchFound = 1
237 END
238
239 END
240 ELSE
241 BEGIN
242 PRINT REPLICATE('-',100)
243 PRINT @tmpTblname
244 PRINT REPLICATE('-',100)
245 PRINT replace(@SQL,'INTO SearchTMP','')
246 END
247
248 UPDATE @SQLTbl
249 SET Execstatus = 1
250 WHERE Tablename = @tmpTblname
251
252 END
253
254 IF @MatchFound = 0
255 BEGIN
256 SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter'
257 PRINT @ErrMsg
258 RETURN
259 END
260
261 SET NOCOUNT OFF
262GO