Sometime
while working with SQL Server we have a value which is in database but we are
not aware which table or which column is containing that value. Try finding
some specific strings in a database with a number of tables, each with many
columns and tens of thousands of records is a difficult thing to do if one tries
to look out manually.
In this case
we can obtain required information from the database by writing SQL query. Let’s
write one stored procedure and then we can execute this stored procedure by
providing search string.
CREATE PROCEDURE
Search_From_AllTables
@SearchValue NVARCHAR(500) -- Search string
AS
BEGIN
/**** Declare @searchQuery ****/
DECLARE @searchQuery
NVARCHAR(MAX) = N''
/**** DROP [#TempResults] table if existing ****/
IF OBJECT_ID('tempdb.dbo.#TempResults') IS NOT NULL
BEGIN
DROP TABLE dbo.#TempResults
END
/**** Create [#TempResults] table ****/
CREATE TABLE dbo.#TempResults
([TableName] NVARCHAR(100),
[ColumnName]
NVARCHAR(100),
[ColumnValue]
NVARCHAR(MAX))
/**** Create @searchQuery to get data with matching
string ****/
SELECT @searchQuery
+= ' SELECT '''
+ S.name + '.' + T.name
+ ''' AS [TableName], ''' +
C.name
+ ''' AS [ColumnName], CAST(' +
QUOTENAME(C.name)
+ ' AS NVARCHAR(max)) AS [ColumnValue] FROM '
+ QUOTENAME(S.name) + '.' + QUOTENAME(T.name) +
' (NOLOCK) WHERE
CAST(' + QUOTENAME(C.name)
+ ' AS nvarchar(max)) LIKE ' +
'''%'
+ @SearchValue + '%'''
FROM sys.Schemas S
INNER JOIN sys.Tables T ON S.schema_id = T.schema_id
INNER JOIN sys.Columns C ON T.object_id = C.object_id
INNER JOIN sys.Types TY ON C.system_type_id = TY.system_type_id
AND C .user_type_id = TY.user_type_id
WHERE T.is_ms_shipped = 0
AND TY.name NOT IN ('timestamp', 'image', 'sql_variant')
/**** Insert into [#TempResults] table by @searchQuery
****/
INSERT dbo.#TempResults
EXEC sp_executesql @searchQuery
/**** Select Matching records to display ****/
SELECT [TableName], [ColumnName], [ColumnValue] FROM dbo.#TempResults
END
|
Now let’s
find the string with the help of this stored procedure as:
EXEC Search_From_AllTables "Search String"
|
That’s
it, hope it’ll solve your problem J
Comments
Post a Comment