SQL Server 全数据库搜索

CREATE PROCEDURE [dbo].[sp_QueryTextInDatabaseContext](@SearchText NVARCHAR(100))
AS

SET NOCOUNT ON
DECLARE
@TableName VARCHAR(60),
@TableColumnName VARCHAR(30),
@TableRowNum INT,
@FoundNum int

print 'Start to query '+@SearchText+' in database context'
print ''

DECLARE UTables CURSOR FOR
Select   a.name,   b.rows   FROM   sysobjects   a   WITH(NOLOCK),   sysindexes   b   WITH(NOLOCK)  
Where   a.xtype   =  'U'   AND   b.indid   IN   (0,   1)   AND   a.id   =   b.id order By a.name

OPEN UTables
FETCH NEXT FROM UTables INTO @TableName,@TableRowNum
WHILE @@FETCH_STATUS = 0
BEGIN
 print @TableName + ':' + CAST(@TableRowNum AS VARCHAR(8))
 IF @TableRowNum = 0
  print 'No data to search'
 ELSE
  BEGIN
   print 'Begin to search...'
   DECLARE UTableColumns CURSOR FOR
   select name
   from syscolumns
   where id=object_id( ''+@TableName )
   OPEN UTableColumns
   FETCH NEXT FROM UTableColumns INTO @TableColumnName
   WHILE @@FETCH_STATUS = 0
   BEGIN
    print 'Search column:'+@TableName+'->'+@TableColumnName
    
    SET @TableColumnName = '['+@TableColumnName+']'
    
    EXEC('SELECT * FROM '+ @TableName + ' WHERE ' + @TableColumnName + ' like ''%'+@SearchText+'%''' )
    SET @FoundNum = @@ROWCOUNT
    IF @FoundNum > 0
     print @SearchText+':'+@TableName+'-'+@TableColumnName+';Affected Rows:'+CAST(@FoundNum AS VARCHAR(8))    
    
    FETCH NEXT FROM UTableColumns INTO @TableColumnName
   END
   CLOSE UTableColumns
   DEALLOCATE UTableColumns
  END
  
 
 print '###############################################################'
 FETCH NEXT FROM UTables INTO @TableName,@TableRowNum
END
CLOSE UTables
DEALLOCATE UTables

 

 

执行:EXEC dbo.sp_QueryTextInDatabaseContext '婴儿的娇嫩皮肤'

在SQL SERVER MS 查询分析器  *右键-> Results to -> Results to file,将查询结果保存在本地文件中

 

附BCP导出查询结果

-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO

declare @command varchar(1000)
set @command = 'BCP "SELECT TOP 10 ch_name FROM database.tablename" queryout D:/myfile.txt -c -U"sa1" -P"password"'

PRINT 'output:'+@command

exec master..xp_cmdshell  @command

 

 

 

 

 

SQLServer : EXEC和sp_executesql的区别

 http://www.cnblogs.com/xbf321/archive/2008/11/02/1325067.html#commentform

 

/****** Object:  StoredProcedure [dbo].[sp_QueryTextInDatabaseContext]    Script Date: 03/28/2011 14:05:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_QueryTextInDatabaseContext](@SearchText NVARCHAR(100))
AS

SET NOCOUNT ON
DECLARE
@TableName VARCHAR(60),
@TableColumnName VARCHAR(30),
@TableRowNum INT,
@stmt nvarchar(160),
@FoundNum int

print 'Start to query '+@SearchText+' in database context'
print ''

DECLARE UTables CURSOR FOR
Select   a.name,   b.rows   FROM   sysobjects   a   WITH(NOLOCK),   sysindexes   b   WITH(NOLOCK)  
Where   a.xtype   =  'U'   AND   b.indid   IN   (0,   1)   AND   a.id   =   b.id order By a.name

OPEN UTables
FETCH NEXT FROM UTables INTO @TableName,@TableRowNum
WHILE @@FETCH_STATUS = 0
BEGIN
    print @TableName + ':' + CAST(@TableRowNum AS VARCHAR(8))
    IF @TableRowNum = 0
        print 'No data to search'
    ELSE
        BEGIN
            print 'Begin to search...'
            DECLARE UTableColumns CURSOR FOR
            select name
            from syscolumns
            where id=object_id( ''+@TableName )
            OPEN UTableColumns
            FETCH NEXT FROM UTableColumns INTO @TableColumnName
            WHILE @@FETCH_STATUS = 0
            BEGIN
                print 'Search column:'+@TableName+'->'+@TableColumnName
               
                SET @TableColumnName = '['+@TableColumnName+']'
                SET @stmt = N'SELECT @Num = count(1) FROM '+ @TableName + ' WHERE ' + @TableColumnName + ' like ''%'+@SearchText+'%'''
                --PRINT @stmt
               
                EXEC sp_executesql
                     @sql = @stmt,
                     @params = N'@Num as int output',
                     @Num = @FoundNum OUTPUT
                     
                IF @FoundNum > 0
                    print @SearchText+':'+@TableName+'-'+@TableColumnName+';Affected Rows:'+CAST(@FoundNum AS VARCHAR(8))               
               
                FETCH NEXT FROM UTableColumns INTO @TableColumnName
            END
            CLOSE UTableColumns
            DEALLOCATE UTableColumns
        END
       
   
    print '###############################################################'
    FETCH NEXT FROM UTables INTO @TableName,@TableRowNum
END
CLOSE UTables
DEALLOCATE UTables