sql server 2008 - 查找哪些表包含特定文本

Question

我有一个包含几百个表的数据库

我需要在所有表的任何列中找到一个特定的字符串。

我将如何解决这个问题>??

Answer 1

查找表列使用:

SELECT table_name=sysobjects.name,
         column_name=syscolumns.name
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U'
ORDER BY sysobjects.name,syscolumns.colid

如果您希望表中的数据使用动态 sql，您可以在这些行上做一些事情:

Declare @Sql varChar(100)
Declare @Temp Table (Ind INT, TableName nvarchar(100))

Insert Into @Temp
Select Row_Number() Over(Order BY [Name]), [Name]
FROM
(
    Select [Name]
    from sys.tables
) AS D

Declare @index int
Set @index = 1
while @index < (Select Count(*) From @Temp)
begin
SET @SQL = ' Select * From ' + (Select TableName From @Temp Where Ind = @index)


exec (@SQL)
Set @index = @index + 1
End