Search All Tables In All Databases On Server For A String
Edit: This question was flagged as a duplicate, but it is not. The other answers on SO show how to search all tables in a single database, I need to search all tables in EVERY da
Solution 1:
First, you have to collect list of all databases' names from sys.databases.
Then you have to create dynamic SQL to extract names of all tables in all databases in format like [database].[schema].[table name]'. You can do it by linking following tables:[database].sys.schemas' & [database].sys.tables'<BR/>
Then you get list of all text columns by linking found tables to[database].sys.columns'
When you get all of that, you can create dynamic queries to all your tables and text columns.
BTW If somebody hide data inside of TEXT column you have to include it in your search and do a conversion.
Solution 2:
Edited :
My answer was a stored procedure . here the query for you :
Change @SearchTerm for your search.
declare@SearchTerm nvarchar(12)
set@SearchTerm='WORD'CREATETABLE #results
(
[database] SYSNAME,
[schema] SYSNAME,
[table] SYSNAME,
[column] SYSNAME,
ExampleValue NVARCHAR(1000)
);
DECLARE@DatabaseCommands NVARCHAR(MAX) = N'',
@ColumnCommands NVARCHAR(MAX) = N'';
SELECT@DatabaseCommands=@DatabaseCommands+ N'
EXEC '+ QUOTENAME(name) +'.sys.sp_executesql
@ColumnCommands, N''@SearchTerm NVARCHAR(MAX)'', @SearchTerm;'FROM sys.databases
WHERE database_id >4-- non-system databasesAND[state] =0-- onlineAND user_access =0; -- multi-userSET@ColumnCommands= N'DECLARE @q NCHAR(1),
@SearchCommands NVARCHAR(MAX);
SELECT @q = NCHAR(39),
@SearchCommands = N''DECLARE @VSearchTerm VARCHAR(255) = @SearchTerm;'';
SELECT @SearchCommands = @SearchCommands + CHAR(10) + N''
SELECT TOP(1)
[db] = DB_NAME(),
[schema] = N'' + @q + s.name + @q + '',
[table] = N'' + @q + t.name + @q + '',
[column] = N'' + @q + c.name + @q + '',
ExampleValue = LEFT('' + QUOTENAME(c.name) + '', 1000)
FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
WHERE '' + QUOTENAME(c.name) + N'' LIKE @'' + CASE
WHEN c.system_type_id IN(35, 167, 175) THEN ''V''
ELSE '''' END + ''SearchTerm;''
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239)
AND c.max_length >= LEN(@SearchTerm);
PRINT @SearchCommands;
EXEC sys.sp_executesql @SearchCommands,
N''@SearchTerm NVARCHAR(255)'', @SearchTerm;';
INSERT #Results
(
[database],
[schema],
[table],
[column],
ExampleValue
)
EXEC[master].sys.sp_executesql @DatabaseCommands,
N'@ColumnCommands NVARCHAR(MAX), @SearchTerm NVARCHAR(255)',
@ColumnCommands, @SearchTerm;
SELECT[Searched for] =@SearchTerm;
SELECT[database],[schema],[table],[column],ExampleValue
FROM #Results
ORDERBY[database],[schema],[table],[column];
Solution 3:
Assuming this is a one time thing here is an alternative to the cursor approach. This will build a dynamic string that you can then execute. It does NOT work for every database but you could either run this for each database manually or modify this to use sp_msforeachdb. You may find that this approach is better though since the undocumented foreachdb will sometimes miss databases. https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb
DECLARE@MySearchCriteriaVARCHAR(500)
SET@MySearchCriteria='''YourSearchStringHere'''--you do need all these quotation marks because this string is injected to another string.SELECT'SELECT '''+ t.name +''' as TableName, '+ c.columnlist +'] FROM ['+ s.name +'].['+ t.name +'] WHERE '+ w.whereclause as SelectStatement
FROM sys.tables t
join sys.schemas s on s.schema_id = t.schema_id
CROSS APPLY (
SELECT STUFF((
SELECT'], ['+ c.Name AS [text()]
FROM sys.columns c
join sys.types t2 on t2.user_type_id = c.user_type_id
WHERE t.object_id = c.object_id
AND c.collation_name ISNOTNULLAND c.max_length >6and t2.name notin ('text', 'ntext')
FOR XML PATH('')
), 1, 2, '' )
) c (columnlist)
CROSS APPLY (
SELECT STUFF((
SELECT' OR ['+ c.Name +'] IN ('+@MySearchCriteria+')'AS [text()]
FROM sys.columns c
join sys.types t2 on t2.user_type_id = c.user_type_id
WHERE t.object_id = c.object_id
AND c.collation_name ISNOTNULLAND c.max_length >6and t2.name notin ('text', 'ntext')
FOR XML PATH('')
), 1, 4, '' )
) w (whereclause)
where c.columnlist isnotnullORDERBY t.name
Solution 4:
I don't know if this would help, it's vb.net code I wrote, it finds the table and field a string is in, potentially searching all DB's - it's just an investigation aid, I've added field types as they've cropped up, but it might give you an idea how I did it anyway
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim scon AsString = String.Format("Data Source={0};Initial Catalog=MASTER;Integrated Security=True", cmbInstance.SelectedItem.ToString)
Button1.Enabled = False
Dim Matches AsNewList(Of Locator)
With ListVresults
.Clear()
.View = View.Details
.Columns.Add("Database", 200)
.Columns.Add("Table", 200)
.Columns.Add("Field", 200)
End With
Using con AsNew SqlConnection(scon),
da AsNew SqlDataAdapter("select *, SCHEMA_NAME(schema_id ) as SkeemaName from sys.tables where type = 'U'", con),
daDB AsNew SqlDataAdapter("select * from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') and state = 0 ORDER BY NAME", con),
dtDB AsNew DataTable
Dim UseLike AsBoolean = chkLIKE.Checked
Dim CompOperator AsString = If(chkLIKE.Checked, "LIKE", "=")
con.Open()
daDB.Fill(dtDB)
For Each drdb As DataRow In dtDB.Select(If(cmbDB.SelectedIndex = 0, "", String.Format("name = '{0}'", cmbDB.SelectedItem.ToString)), "name")
'For Each drdb As DataRow In dtDB.Rows
con.ChangeDatabase(drdb!name.ToString)
Using dt As New DataTable
da.Fill(dt)
For Each dr As DataRow In dt.Select("", "name")
lblProc.Text = String.Format("({2} - {0} - {1}", drdb!name.ToString, dr!name.ToString, Matches.Count)
Application.DoEvents()
Dim sql As New System.Text.StringBuilder
Using dsDatNull As New DataSet,
daDatNull As New SqlDataAdapter(String.Format("SELECT * FROM [{0}].[{1}] WHERE 1=0; SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", dr!skeemaName, dr!name), con)
daDatNull.Fill(dsDatNull)
'SELECT * FROM(INFORMATION_SCHEMA.COLUMNS) WHERE TABLE_NAME = 'absence'
sql.AppendFormat("SELECT * FROM [{0}].[{1}] WHERE ", dr!skeemaname, dr!name)
Dim bOR AsBoolean = False
Dim col As DataColumn
Dim Q As System.Data.EnumerableRowCollection(Of System.Data.DataRow) = (From x As DataRow In dsDatNull.Tables(1) Where String.Compare(x!column_name.ToString, col.ColumnName, True) = 0 Select x)
For Each col In dsDatNull.Tables(0).Columns
Dim drColType As DataRow = Q(0)
Dim dataType AsString = drColType!Data_Type.ToString
If"x".GetType Is col.DataType AndAlso (String.Compare(dataType, "nvarchar", True) = 0 _
OrElse String.Compare(dataType, "varchar", True) = 0 _
OrElse String.Compare(dataType, "nchar", True) = 0 _
OrElse String.Compare(dataType, "char", True) = 0) Then
If bOR Then sql.Append(" OR ")
bOR = True
sql.Append("[" + col.ColumnName + "]").Append(If(UseLike, " LIKE ", " = ")).AppendFormat("'{0}{1}{2}'", If(UseLike, "%", ""), txtCode.Text.Replace("'", "''"), If(UseLike, "%", ""))
ElseIf (New Guid).GetType Is col.DataType Then
If bOR Then sql.Append(" OR ")
bOR = True
sql.Append("cast([" + col.ColumnName + "] as nvarchar(80))").Append(If(UseLike, " LIKE ", " = ")).AppendFormat("'{0}{1}{2}'", If(UseLike, "%", ""), txtCode.Text.Replace("'", "''"), If(UseLike, "%", ""))
End If
Next
If Not bOR Then sql.Append(" 1=0 ")
End Using
Using dtDat AsNew DataTable,
daDat AsNew SqlDataAdapter(sql.ToString, con)
daDat.SelectCommand.CommandTimeout = 3600
daDat.Fill(dtDat)
For Each drDat As DataRow In dtDat.Rows
For Each col As DataColumn In dtDat.Columns
Dim obj = drDat(col)
IfString.Compare(obj.ToString, txtCode.Text, True) = 0 OrElse UseLike And obj.ToString.ToLower.Contains(txtCode.Text.ToLower) Then
If Not (From x As Locator In Matches Where x.DB.ToString = drdb!name.ToString And x.Table = dr!name.ToString And x.Column = col.ColumnName).Any Then
Dim newOne AsNew Locator(drdb!name.ToString, dr!name.ToString, col.ToString)
Matches.Add(newOne)
With newOne
ListVresults.Items.Add(New ListViewItem({.DB, .Table, .Column}))
End With
End If
End If
Next
Next
End Using
Next
End Using
Next
End Using
lblProc.Text = ""
Button1.Enabled = True
End Sub
Solution 5:
This is the script I always use... set the string to search for at the top (see comment) and let it run.
DECLARE@tableName sysname
DECLARE@columnName sysname
DECLARE@valuevarchar(100)
DECLARE@sqlvarchar(2000)
DECLARE@sqlPreamblevarchar(100)
DECLARE@minLengthint;
SET@value='%SomeString%'-- *** Set this to the value you're searching for *** --SET@minLength= LEN(REPLACE(@value, '%', ''));
SET@sqlPreamble='IF EXISTS (SELECT 1 FROM 'DECLARE theTableCursor CURSOR FAST_FORWARD FORSELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA ='dbo'AND TABLE_TYPE ='BASE TABLE'AND TABLE_NAME !='dtproperties'AND TABLE_NAME !='sysdiagrams'ORDERBY TABLE_NAME
OPEN theTableCursor
FETCH NEXT FROM theTableCursor INTO@tableName
WHILE @@FETCH_STATUS =0-- spin through Table entriesBEGINDECLARE theColumnCursor CURSOR FAST_FORWARD FORSELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME =@tableNameAND (DATA_TYPE ='nvarchar'OR DATA_TYPE ='varchar')
AND (CHARACTER_MAXIMUM_LENGTH >=@minlengthOR CHARACTER_MAXIMUM_LENGTH =-1)
ORDERBY ORDINAL_POSITION
OPEN theColumnCursor
FETCH NEXT FROM theColumnCursor INTO@columnName
WHILE @@FETCH_STATUS =0-- spin through Column entriesBEGINSET@sql= N'['+@tableName+ N'] (nolock) WHERE ['+@columnName+ N'] LIKE '''+@value+
N''') PRINT ''Value found in Table: '+@tableName+ N', Column: '+@columnName+ N''''EXEC (@sqlPreamble+@sql)
FETCH NEXT FROM theColumnCursor INTO@columnNameENDCLOSE theColumnCursor
DEALLOCATE theColumnCursor
FETCH NEXT FROM theTableCursor INTO@tableNameENDCLOSE theTableCursor
DEALLOCATE theTableCursor
Post a Comment for "Search All Tables In All Databases On Server For A String"