可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
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 database on a given server.
I need to search all tables for all databases on a server for a search string. I've got email address littered throughout tables that are going to have a change of domain and I need to prepare a report that shows where these email addresses are located. I am not going to be able to add a stored procedure to all the databases so I need a query to do this that's doesn't involve exec-ing a sp repeatedly. I pulled this code off the net and was using it to search all tables but I haven't been able to figure out how to run it on all databases.
drop table #Results
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @SearchStr nvarchar(100), @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @SearchStr = '@domaintobereplaced.com'
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 top 10 ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
回答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.
回答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'
CREATE TABLE #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 databases
AND[state] = 0-- online
AND user_access = 0; -- multi-user
SET @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
ORDER BY[database],[schema],[table],[column];
回答3:
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 As String = String.Format("Data Source={0};Initial Catalog=MASTER;Integrated Security=True", cmbInstance.SelectedItem.ToString)
Button1.Enabled = False
Dim Matches As New List(Of Locator)
With ListVresults
.Clear()
.View = View.Details
.Columns.Add("Database", 200)
.Columns.Add("Table", 200)
.Columns.Add("Field", 200)
End With
Using con As New SqlConnection(scon),
da As New SqlDataAdapter("select *, SCHEMA_NAME(schema_id ) as SkeemaName from sys.tables where type = 'U'", con),
daDB As New SqlDataAdapter("select * from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') and state = 0 ORDER BY NAME", con),
dtDB As New DataTable
Dim UseLike As Boolean = chkLIKE.Checked
Dim CompOperator As String = 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 As Boolean = 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 As String = 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 As New DataTable,
daDat As New 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)
If String.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 As New 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
回答4:
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 @value varchar(100)
DECLARE @sql varchar(2000)
DECLARE @sqlPreamble varchar(100)
DECLARE @minLength int;
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 FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME != 'dtproperties' AND TABLE_NAME != 'sysdiagrams'
ORDER BY TABLE_NAME
OPEN theTableCursor
FETCH NEXT FROM theTableCursor INTO @tableName
WHILE @@FETCH_STATUS = 0 -- spin through Table entries
BEGIN
DECLARE theColumnCursor CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
AND (DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar')
AND (CHARACTER_MAXIMUM_LENGTH >= @minlength OR CHARACTER_MAXIMUM_LENGTH = -1)
ORDER BY ORDINAL_POSITION
OPEN theColumnCursor
FETCH NEXT FROM theColumnCursor INTO @columnName
WHILE @@FETCH_STATUS = 0 -- spin through Column entries
BEGIN
SET @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 @columnName
END
CLOSE theColumnCursor
DEALLOCATE theColumnCursor
FETCH NEXT FROM theTableCursor INTO @tableName
END
CLOSE theTableCursor
DEALLOCATE theTableCursor
回答5:
This question is a bit old at this point and most of the answers seem to miss the mark in terms of searching ALL servers and ALL databases on each server. Turned out my manager was just trying to give me an impossible and time consuming task aka busy work to stall me because our team was getting disbanded in a re-org and we were all being sent to different parts in the company.
So, for the sake of writing a comprehensive answer I will say my approach now would be to create a console application that accepts a comma separated list of server names. It would loop through all the server names and connect to them one by one. Once in a server I would query for and loop through all the user created databases and run the search all tables query on each one. If results were found I would write them to a file stored in a directory with a [ServerName]_[DatabaseName]_Results title and then use the outputted list of files to review and develop and action plan for further querying/remediation. It doesn't appear from the answers above that there is a way to do this in SQL Server.
回答6:
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 @MySearchCriteria VARCHAR(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 IS NOT NULL
AND c.max_length > 6
and t2.name not in ('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 IS NOT NULL
AND c.max_length > 6
and t2.name not in ('text', 'ntext')
FOR XML PATH('')
), 1, 4, '' )
) w (whereclause)
where c.columnlist is not null
ORDER BY t.name
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://img.manongdao.com/sparkler-677774__340.jpg', '推荐 再贱就再见 的文章《Search all tables in all databases on server for a》','https://www.manongdao.com/article-482855.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}