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