搜索某个信息所在的表及栏位

有时，我们需要在一个数据库里搜索某个信息所在的表及栏位，可以通过如下SQL实现：

--Variable Delaration
Declare @keytosearch varchar(max), @Database_Selected varchar(50)

set @keytosearch =N'%搜索关键字%' 
set @Database_Selected= N'数据库名称'

Declare @Table varchar(100), @Table_Name Cursor, @Count_Column int, @Result nvarchar(4000),@ID int,@ID_inserted int,@Count_Table int, @data_type varchar(10)

set @ID_inserted=0   
set @Count_Table=0

DECLARE @column varchar(max), @Column_Name CURSOR
--Variable Delaration end

--Second Cursor start
declare @informationName varchar(50), @SysName varchar(50), @Var varchar(5)

set @informationName=@Database_Selected+'.'+'information_schema.COLUMNS'
Set @SysName=@Database_Selected+'.'+'sys.objects'
Set @Var='u'

--Database Selected start
Create Table #SearchResult(ID int identity(1,1) not null, TableName varchar(50), ColumnName varchar(50))
Create Table #SearchTestResult(ID int identity(1,1) not null, ColumnName varchar(max))
Create Table #Table_Name(ID int identity(1,1) not null, table_name varchar(50))

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Column_Nam') 
  drop table Column_Nam

insert into #Table_Name exec ('Select name from '+@SysName +' where type= '''+@Var+'''')

--First Cursor open
SET @Table_Name = CURSOR FOR Select table_name from #Table_Name 

open @Table_Name 

Fetch Next from @Table_Name into @Table

WHILE @@FETCH_STATUS = 0
 BEGIN 
  set @Count_Table =@Count_Table+1
  --Second cursor opened
  --print 'Select column_name from  '+@informationName +' where table_name= '''+@Table+''''s
  Exec('SELECT column_name,data_type INTO Column_Nam FROM  '+ @informationName +' where table_name = '''+@Table+'''')
  SET @Column_Name = CURSOR FOR (select column_name from Column_Nam )

  OPEN @Column_Name FETCH NEXT FROM @Column_Name INTO @column
 
  set @Table=@Database_Selected+'.[dbo].['+@Table+']'
  WHILE @@FETCH_STATUS = 0
   BEGIN
    set @data_type= (SELECT top 1 data_type FROM Column_Nam Where column_name= @column)

    if @data_type is not null and (@data_type='varchar' or @data_type='nvarchar' or @data_type='Text')
     begin 
      set @Result=null
      if @column like '%-%' begin  set @Result =('SELECT ''' + @column +''' FROM ' + @Table  
                +' Where ''' + @column + ''' Like '''+@keytosearch+'''') end
        else
		 set @Result =('SELECT ' + @column +' FROM ' + @Table  +' Where ' + @column + ' Like '''+@keytosearch+'''')

      insert into #SearchTestResult exec(@Result)

      set @ID=0
      set @ID=(Select ID from #SearchTestResult where ID=Scope_identity())

	  if @ID is not null 
	   begin
        set @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)
            if @ID_inserted = @ID 
             begin 
              print '' 
             end 
           else  
	        insert into #SearchResult values (@Table,@column)
       end  
    end
    FETCH NEXT FROM @Column_Name INTO @column 
   END  
  CLOSE @Column_Name  
  DEALLOCATE @Column_Name
  --Second cursor closed

  drop table Column_Nam

  Fetch Next from @Table_Name into @Table 
 End 

close @Table_Name  

Deallocate @Table_Name
--First Cursor Closed

Select * from #SearchResult