SQL server 2008查询数据库中含有此字段内容的所有表,并将表与字段插入到临时表中。存储过程如下:
USE [cwbase27]
GO
/****** Object: StoredProcedure [dbo].[global_search] Script Date: 01/13/2015 11:05:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[global_search]
@strValue nvarchar(100)--search value
as
declare @tab_name nvarchar(300),@col_name sysname,@sql nvarchar(1000)
begin
declare curTab_cursor cursor fast_forward
for
select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id
where o.type_desc='user_table' and c.user_type_id in(56,167,175,231,239)
--user_type_id Only check int varchar char nvarchar nchar five types, if you need can be extended
end
open curTab