查找某数据库中的列是否存在某个值alter proc spFind_Column_In_DB ( @type int,--类型:1为文字类型、2为数值类型 @str nvarchar(100)--需要搜索的名字 ) as --创建临时表存放结果 create table #tbl(PK int identity primary key ,tbl sysname,col sysname) declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000) if @type=1 begin declare curTable 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 user_type_id in (167,175,231,239,35,99) end else begin declare curTable 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 user_type_id in (56,48,52,59,60,62,106,108,122) end open curtable fetch next from curtable into @tbl,@col while @@FETCH_STATUS=0 begin set @sql='if exists (select * from '+@tbl+' where ' if @type=1 begin set @sql += @col + ' like ''%'+@str +'%'')' end else begin set @sql +=@col + ' in ('+@str+'))' end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')' --print @sql exec (@sql) fetch next from curtable into @tbl,@col end close curtable deallocate curtable select * from #tbl
alter proc spFind_Column_In_DB ( @type int,--12 @str nvarchar(3005)-- ) as -- create table #tbl(PK int identity primary key ,tbl sysname,col sysname) declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000) if @type=1 begin declare curTable 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 user_type_id in (167,175,231,239,35,99) end else begin declare curTable 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 user_type_id in (56,48,52,59,60,62,106,108,122) end open curtable fetch next from curtable into @tbl,@col while @@FETCH_STATUS=0 begin set @sql='if exists (select * from '+@tbl+' where ' if @type=1 begin set @sql += @col + ' like ''%'+@str +'%'')' end else begin set @sql +=@col + ' in ('+@str+'))' end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')' --print @sql exec (@sql) fetch next from curtable into @tbl,@col end close curtable deallocate curtable select * from #tbl 按你上面我给于值是3005但是查询提示 : 195 15 10 spFind_Column_In_DB 14 'SCHEMA_NAME' : 195 15 1 spFind_Column_In_DB 21 'SCHEMA_NAME' : 170 15 1 spFind_Column_In_DB 31 31 : '+' : 170 15 1 spFind_Column_In_DB 35 35 : '+' : 170 15 1 spFind_Column_In_DB 38 38 : '+'
再试试alter proc spFind_Column_In_DB ( @type int,--类型:1为文字类型、2为数值类型 @str nvarchar(100)--需要搜索的名字 ) as --创建临时表存放结果 create table #tbl(PK int identity primary key ,tbl sysname,col sysname) declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000) if @type=1 begin declare curTable cursor fast_forward for select '['+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 user_type_id in (167,175,231,239,35,99) end else begin declare curTable cursor fast_forward for select '['+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 user_type_id in (56,48,52,59,60,62,106,108,122) end open curtable fetch next from curtable into @tbl,@col while @@FETCH_STATUS=0 begin set @sql='if exists (select * from '+@tbl+' where ' if @type=1 begin set @sql += @col + ' like ''%'+@str +'%'')' end else begin set @sql +=@col + ' in ('+@str+'))' end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')' --print @sql exec (@sql) fetch next from curtable into @tbl,@col end close curtable deallocate curtable select * from #tbl
希望你的库不要有什么乱七八糟的命名alter proc spFind_Column_In_DB ( @type int,--类型:1为文字类型、2为数值类型 @str nvarchar(100)--需要搜索的名字 ) as --创建临时表存放结果 create table #tbl(PK int identity primary key ,tbl sysname,col sysname) declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000) if @type=1 begin declare curTable cursor fast_forward for select 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 user_type_id in (167,175,231,239,35,99) end else begin declare curTable cursor fast_forward for select 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 user_type_id in (56,48,52,59,60,62,106,108,122) end open curtable fetch next from curtable into @tbl,@col while @@FETCH_STATUS=0 begin set @sql='if exists (select * from '+@tbl+' where ' if @type=1 begin set @sql += @col + ' like ''%'+@str +'%'')' end else begin set @sql +=@col + ' in ('+@str+'))' end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')' --print @sql exec (@sql) fetch next from curtable into @tbl,@col end close curtable deallocate curtable select * from #tbl
alter proc spFind_Column_In_DB 2,'3005' ( @type int,--类型:1为文字类型、2为数值类型 @str nvarchar(100)--需要搜索的名字 ) as --创建临时表存放结果 create table #tbl(PK int identity primary key ,tbl sysname,col sysname) declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000) if @type=1 begin declare curTable cursor fast_forward for select 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 user_type_id in (167,175,231,239,35,99) end else begin declare curTable cursor fast_forward for select 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 user_type_id in (56,48,52,59,60,62,106,108,122) end open curtable fetch next from curtable into @tbl,@col while @@FETCH_STATUS=0 begin set @sql='if exists (select * from '+@tbl+' where ' if @type=1 begin set @sql += @col + ' like ''%'+@str +'%'')' end else begin set @sql +=@col + ' in ('+@str+'))' end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')' --print @sql exec (@sql) fetch next from curtable into @tbl,@col end close curtable deallocate curtable select * from #tbl
alter proc spFind_Column_In_DB ( @type int,--类型:1为文字类型、2为数值类型 @str nvarchar(3005)--需要搜索的名字 ) as --创建临时表存放结果 create table #tbl(PK int identity primary key ,tbl sysname,col sysname) declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000) if @type=1 begin declare curTable cursor fast_forward for select 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 user_type_id in (167,175,231,239,35,99) end else begin declare curTable cursor fast_forward for select 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 user_type_id in (56,48,52,59,60,62,106,108,122) end open curtable fetch next from curtable into @tbl,@col while @@FETCH_STATUS=0 begin set @sql='if exists (select * from '+@tbl+' where ' if @type=1 begin set @sql += @col + ' like ''%'+@str +'%'')' end else begin set @sql +=@col + ' in ('+@str+'))' end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')' --print @sql exec (@sql) fetch next from curtable into @tbl,@col end close curtable deallocate curtable select * from #tbl我是这样!新手!!理解!
步骤1:打开查询分析器,输入:use Local go drop proc spFind_Column_In_DB go create proc spFind_Column_In_DB ( @type int,--类型:1为文字类型、2为数值类型 @str nvarchar(100)--需要搜索的名字 ) as --创建临时表存放结果 create table #tbl(PK int identity primary key ,tbl sysname,col sysname) declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000) if @type=1 begin declare curTable cursor fast_forward for select 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 user_type_id in (167,175,231,239,35,99) end else begin declare curTable cursor fast_forward for select 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 user_type_id in (56,48,52,59,60,62,106,108,122) end open curtable fetch next from curtable into @tbl,@col while @@FETCH_STATUS=0 begin set @sql='if exists (select * from '+@tbl+' where ' if @type=1 begin set @sql += @col + ' like ''%'+@str +'%'')' end else begin set @sql +=@col + ' in ('+@str+'))' end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')' --print @sql exec (@sql) fetch next from curtable into @tbl,@col end close curtable deallocate curtable select * from #tbl然后按F5 步骤2:打开新的查询分析器界面,输入: use local go spFind_Column_In_DB 2,'3005'再按F5
第一步,再试试use Local go drop proc spFind_Column_In_DB go create proc spFind_Column_In_DB ( @type int,--类型:1为文字类型、2为数值类型 @str nvarchar(100)--需要搜索的名字 ) as --创建临时表存放结果 create table #tbl(PK int identity primary key ,tbl sysname,col sysname) declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000) if @type=1 begin declare curTable cursor fast_forward for select 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 user_type_id in (167,175,231,239,35,99) end else begin declare curTable cursor fast_forward for select 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 user_type_id in (56,48,52,59,60,62,106,108,122) end open curtable fetch next from curtable into @tbl,@col while @@FETCH_STATUS=0 begin set @sql='if exists (select * from '+@tbl+' where ' if @type=1 begin set @sql =@sql+ @col + ' like ''%'+@str +'%'')' end else begin set @sql =@sql+@col + ' in ('+@str+'))' end
set @sql =@sql+ ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')' --print @sql exec (@sql) fetch next from curtable into @tbl,@col end close curtable deallocate curtable select * from #tbl
use Local go create proc spFind_Column_In_DB ( @type int,--类型:1为文字类型、2为数值类型 @str nvarchar(100)--需要搜索的名字 ) as --创建临时表存放结果 create table #tbl(PK int identity primary key ,tbl sysname,col sysname) declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000) if @type=1 begin declare curTable cursor fast_forward for select 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 user_type_id in (167,175,231,239,35,99) end else begin declare curTable cursor fast_forward for select 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 user_type_id in (56,48,52,59,60,62,106,108,122) end open curtable fetch next from curtable into @tbl,@col while @@FETCH_STATUS=0 begin set @sql='if exists (select * from '+@tbl+' where ' if @type=1 begin set @sql =@sql+ @col + ' like ''%'+@str +'%'')' end else begin set @sql =@sql+@col + ' in ('+@str+'))' end
set @sql =@sql+ ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')' --print @sql exec (@sql) fetch next from curtable into @tbl,@col end close curtable deallocate curtable select * from #tbl
alter proc spFind_Column_In_DB ( @type int,--类型:1为文字类型、2为数值类型 @str nvarchar(100)--需要搜索的名字 ) as --创建临时表存放结果 create table #tbl(PK int identity primary key ,tbl sysname,col sysname) declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000) if @type=1 begin declare curTable cursor fast_forward for select o.name tableName,c.name columnName from information_schema.columns c inner join sysobjects o on c.object_id=o.object_id where o.type_desc='user_table' and user_type_id in (167,175,231,239,35,99) end else begin declare curTable cursor fast_forward for select o.name tableName,c.name columnName from information_schema.columns c inner join sysobjects o on c.object_id=o.object_id where o.type_desc='user_table' and user_type_id in (56,48,52,59,60,62,106,108,122) end open curtable fetch next from curtable into @tbl,@col while @@FETCH_STATUS=0 begin set @sql='if exists (select * from '+@tbl+' where ' if @type=1 begin set @sql =@sql+ @col + ' like ''%'+@str +'%'')' end else begin set @sql =@sql+@col + ' in ('+@str+'))' end
set @sql =@sql+ ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')' --print @sql exec (@sql) fetch next from curtable into @tbl,@col end close curtable deallocate curtable select * from #tbl
你确定测试了我发的那个吗?declare @str varchar(100) set @str='a' --要搜索的字符串declare @s varchar(8000) declare tb cursor local for select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'') print '' ['+b.name+'].['+a.name+']''' from syscolumns a join sysobjects b on a.id=b.id where b.xtype='U' and a.status>=0 and a.xusertype in(175,239,231,167) open tb fetch next from tb into @s while @@fetch_status=0 begin exec(@s) fetch next from tb into @s end close tb deallocate tb
declare @str varchar(100) set @str='3005' --要搜索的字符串declare @s varchar(8000) declare tb cursor local for select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'') print '' ['+b.name+'].['+a.name+']''' from syscolumns a join sysobjects b on a.id=b.id where b.xtype='U' and a.status>=0 and a.xusertype in(175,239,231,167) open tb fetch next from tb into @s while @@fetch_status=0 begin exec(@s) fetch next from tb into @s end close tb deallocate tb执行之后什么也没有显示!
(
@type int,--类型:1为文字类型、2为数值类型
@str nvarchar(100)--需要搜索的名字
)
as
--创建临时表存放结果
create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
if @type=1
begin
declare curTable 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 user_type_id in (167,175,231,239,35,99)
end
else
begin
declare curTable 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 user_type_id in (56,48,52,59,60,62,106,108,122)
end
open curtable
fetch next from curtable into @tbl,@col
while @@FETCH_STATUS=0
begin
set @sql='if exists (select * from '+@tbl+' where '
if @type=1
begin
set @sql += @col + ' like ''%'+@str +'%'')'
end
else
begin
set @sql +=@col + ' in ('+@str+'))'
end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
--print @sql
exec (@sql)
fetch next from curtable into @tbl,@col
end
close curtable
deallocate curtable
select * from #tbl
(
@type int,--12
@str nvarchar(3005)--
)
as
--
create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
if @type=1
begin
declare curTable 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 user_type_id in (167,175,231,239,35,99)
end
else
begin
declare curTable 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 user_type_id in (56,48,52,59,60,62,106,108,122)
end
open curtable
fetch next from curtable into @tbl,@col
while @@FETCH_STATUS=0
begin
set @sql='if exists (select * from '+@tbl+' where '
if @type=1
begin
set @sql += @col + ' like ''%'+@str +'%'')'
end
else
begin
set @sql +=@col + ' in ('+@str+'))'
end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
--print @sql
exec (@sql)
fetch next from curtable into @tbl,@col
end
close curtable
deallocate curtable
select * from #tbl
按你上面我给于值是3005但是查询提示
: 195 15 10 spFind_Column_In_DB 14
'SCHEMA_NAME'
: 195 15 1 spFind_Column_In_DB 21
'SCHEMA_NAME'
: 170 15 1 spFind_Column_In_DB 31
31 : '+'
: 170 15 1 spFind_Column_In_DB 35
35 : '+'
: 170 15 1 spFind_Column_In_DB 38
38 : '+'
spFind_Column_In_DB 2,'3005'
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 1
第 1 行: '2' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,过程 spFind_Column_In_DB,行 10
必须声明变量 '@type'。
服务器: 消息 195,级别 15,状态 1,过程 spFind_Column_In_DB,行 14
'SCHEMA_NAME' 不是可以识别的 函数名。
服务器: 消息 195,级别 15,状态 1,过程 spFind_Column_In_DB,行 21
'SCHEMA_NAME' 不是可以识别的 函数名。
服务器: 消息 137,级别 15,状态 1,过程 spFind_Column_In_DB,行 29
必须声明变量 '@type'。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 31
第 31 行: '+' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 35
第 35 行: '+' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 38
第 38 行: '+' 附近有语法错误。
(
@type int,--类型:1为文字类型、2为数值类型
@str nvarchar(100)--需要搜索的名字
)
as
--创建临时表存放结果
create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
if @type=1
begin
declare curTable cursor fast_forward
for
select '['+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 user_type_id in (167,175,231,239,35,99)
end
else
begin
declare curTable cursor fast_forward
for
select '['+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 user_type_id in (56,48,52,59,60,62,106,108,122)
end
open curtable
fetch next from curtable into @tbl,@col
while @@FETCH_STATUS=0
begin
set @sql='if exists (select * from '+@tbl+' where '
if @type=1
begin
set @sql += @col + ' like ''%'+@str +'%'')'
end
else
begin
set @sql +=@col + ' in ('+@str+'))'
end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
--print @sql
exec (@sql)
fetch next from curtable into @tbl,@col
end
close curtable
deallocate curtable
select * from #tbl
第 31 行: '+' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 35
第 35 行: '+' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 38
第 38 行: '+' 附近有语法错误。
(
@type int,--类型:1为文字类型、2为数值类型
@str nvarchar(100)--需要搜索的名字
)
as
--创建临时表存放结果
create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
if @type=1
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (167,175,231,239,35,99)
end
else
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (56,48,52,59,60,62,106,108,122)
end
open curtable
fetch next from curtable into @tbl,@col
while @@FETCH_STATUS=0
begin
set @sql='if exists (select * from '+@tbl+' where '
if @type=1
begin
set @sql += @col + ' like ''%'+@str +'%'')'
end
else
begin
set @sql +=@col + ' in ('+@str+'))'
end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
--print @sql
exec (@sql)
fetch next from curtable into @tbl,@col
end
close curtable
deallocate curtable
select * from #tbl
第 31 行: '+' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 35
第 35 行: '+' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 38
第 38 行: '+' 附近有语法错误。还是提示这个错误,没变
spFind_Column_In_DB 2,'3005'
要是是,那我已经无能为力了。
@type int,--类型:1为文字类型、2为数值类型
@str nvarchar(100)--需要搜索的名字
)
as
--创建临时表存放结果
create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
if @type=1
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (167,175,231,239,35,99)
end
else
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (56,48,52,59,60,62,106,108,122)
end
open curtable
fetch next from curtable into @tbl,@col
while @@FETCH_STATUS=0
begin
set @sql='if exists (select * from '+@tbl+' where '
if @type=1
begin
set @sql += @col + ' like ''%'+@str +'%'')'
end
else
begin
set @sql +=@col + ' in ('+@str+'))'
end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
--print @sql
exec (@sql)
fetch next from curtable into @tbl,@col
end
close curtable
deallocate curtable
select * from #tbl
第 1 行: '2' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,过程 spFind_Column_In_DB,行 11
必须声明变量 '@type'。
服务器: 消息 156,级别 15,状态 1,过程 spFind_Column_In_DB,行 18
在关键字 'else' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,过程 spFind_Column_In_DB,行 30
必须声明变量 '@type'。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 32
第 32 行: '+' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 36
第 36 行: '+' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 39
第 39 行: '+' 附近有语法错误。
(
@type int,--类型:1为文字类型、2为数值类型
@str nvarchar(3005)--需要搜索的名字
)
as
--创建临时表存放结果
create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
if @type=1
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (167,175,231,239,35,99)
end
else
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (56,48,52,59,60,62,106,108,122)
end
open curtable
fetch next from curtable into @tbl,@col
while @@FETCH_STATUS=0
begin
set @sql='if exists (select * from '+@tbl+' where '
if @type=1
begin
set @sql += @col + ' like ''%'+@str +'%'')'
end
else
begin
set @sql +=@col + ' in ('+@str+'))'
end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
--print @sql
exec (@sql)
fetch next from curtable into @tbl,@col
end
close curtable
deallocate curtable
select * from #tbl我是这样!新手!!理解!
spFind_Column_In_DB 2,'3005'其他啥都不要加
步骤1:打开查询分析器,输入:use Local
go
drop proc spFind_Column_In_DB
go
create proc spFind_Column_In_DB
(
@type int,--类型:1为文字类型、2为数值类型
@str nvarchar(100)--需要搜索的名字
)
as
--创建临时表存放结果
create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
if @type=1
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (167,175,231,239,35,99)
end
else
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (56,48,52,59,60,62,106,108,122)
end
open curtable
fetch next from curtable into @tbl,@col
while @@FETCH_STATUS=0
begin
set @sql='if exists (select * from '+@tbl+' where '
if @type=1
begin
set @sql += @col + ' like ''%'+@str +'%'')'
end
else
begin
set @sql +=@col + ' in ('+@str+'))'
end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
--print @sql
exec (@sql)
fetch next from curtable into @tbl,@col
end
close curtable
deallocate curtable
select * from #tbl然后按F5
步骤2:打开新的查询分析器界面,输入:
use local
go
spFind_Column_In_DB 2,'3005'再按F5
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 31
第 31 行: '+' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 35
第 35 行: '+' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 spFind_Column_In_DB,行 38
第 38 行: '+' 附近有语法错误。
go
drop proc spFind_Column_In_DB
go
create proc spFind_Column_In_DB
(
@type int,--类型:1为文字类型、2为数值类型
@str nvarchar(100)--需要搜索的名字
)
as
--创建临时表存放结果
create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
if @type=1
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (167,175,231,239,35,99)
end
else
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (56,48,52,59,60,62,106,108,122)
end
open curtable
fetch next from curtable into @tbl,@col
while @@FETCH_STATUS=0
begin
set @sql='if exists (select * from '+@tbl+' where '
if @type=1
begin
set @sql =@sql+ @col + ' like ''%'+@str +'%'')'
end
else
begin
set @sql =@sql+@col + ' in ('+@str+'))'
end
set @sql =@sql+ ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
--print @sql
exec (@sql)
fetch next from curtable into @tbl,@col
end
close curtable
deallocate curtable
select * from #tbl
服务器: 消息 3701,级别 11,状态 5,行 1
无法 除去 过程 'spFind_Column_In_DB',因为它在系统目录中不存在。
go create proc spFind_Column_In_DB
(
@type int,--类型:1为文字类型、2为数值类型
@str nvarchar(100)--需要搜索的名字
)
as
--创建临时表存放结果
create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
if @type=1
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (167,175,231,239,35,99)
end
else
begin
declare curTable cursor fast_forward
for
select 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 user_type_id in (56,48,52,59,60,62,106,108,122)
end
open curtable
fetch next from curtable into @tbl,@col
while @@FETCH_STATUS=0
begin
set @sql='if exists (select * from '+@tbl+' where '
if @type=1
begin
set @sql =@sql+ @col + ' like ''%'+@str +'%'')'
end
else
begin
set @sql =@sql+@col + ' in ('+@str+'))'
end
set @sql =@sql+ ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
--print @sql
exec (@sql)
fetch next from curtable into @tbl,@col
end
close curtable
deallocate curtable
select * from #tbl
数据库中已存在名为 'spFind_Column_In_DB' 的对象。
对象名 'sys.columns' 无效。
服务器: 消息 208,级别 16,状态 1,过程 spFind_Column_In_DB,行 21
对象名 'sys.objects' 无效。
use Local
go
alter proc spFind_Column_In_DB
(
@type int,--类型:1为文字类型、2为数值类型
@str nvarchar(100)--需要搜索的名字
)
as
--创建临时表存放结果
create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
if @type=1
begin
declare curTable cursor fast_forward
for
select o.name tableName,c.name columnName from information_schema.columns c inner join sysobjects o on c.object_id=o.object_id
where o.type_desc='user_table' and user_type_id in (167,175,231,239,35,99)
end
else
begin
declare curTable cursor fast_forward
for
select o.name tableName,c.name columnName from information_schema.columns c inner join sysobjects o on c.object_id=o.object_id
where o.type_desc='user_table' and user_type_id in (56,48,52,59,60,62,106,108,122)
end
open curtable
fetch next from curtable into @tbl,@col
while @@FETCH_STATUS=0
begin
set @sql='if exists (select * from '+@tbl+' where '
if @type=1
begin
set @sql =@sql+ @col + ' like ''%'+@str +'%'')'
end
else
begin
set @sql =@sql+@col + ' in ('+@str+'))'
end
set @sql =@sql+ ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
--print @sql
exec (@sql)
fetch next from curtable into @tbl,@col
end
close curtable
deallocate curtable
select * from #tbl
服务器: 消息 207,级别 16,状态 3,过程 spFind_Column_In_DB,行 15
列名 'object_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 15
列名 'object_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 15
列名 'name' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 15
列名 'type_desc' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 15
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 15
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 15
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 15
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 15
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 15
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'object_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'object_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'name' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'type_desc' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'user_type_id' 无效。
服务器: 消息 207,级别 16,状态 1,过程 spFind_Column_In_DB,行 22
列名 'user_type_id' 无效。
set @str='a' --要搜索的字符串declare @s varchar(8000)
declare tb cursor local for
select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print '' ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
set @str='3005' --要搜索的字符串declare @s varchar(8000)
declare tb cursor local for
select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
print '' ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb执行之后什么也没有显示!