Sql遍历数据库
标签：
Sql遍历数据库
nocount on
Declare @Str varchar(50)
Declare @CName varchar(50)
Declare @CID varchar(20)
set @str=‘ad‘
Declare cur_Depart Cursor
For select name,id from syscolumns where id in (Select id From sysobjects Where xtype =‘U‘) and xtype in (select xtype from systypes where name in ( ‘varchar‘, ‘nvarchar‘, ‘char‘, ‘nchar‘,‘text‘)) order by id
Open cur_Depart
Fetch From cur_Depart into @CName,@CID
While @@Fetch_Status=0
Begin
Declare @Sql nvarchar(500)
Declare @TName nvarchar(500)
Declare @Count int
set @TName=(Select name From sysobjects Where ID=@CID)
set @Sql=‘select @c=Count(*) from ‘+@TName+‘ WHERE PATINDEX(‘‘%‘+@Str+‘%‘‘,‘+@CName+‘)>0‘
exec sp_executesql @Sql,N‘@c int output‘,@Count output
if (@Count>0) print @TName+‘ ‘+@CName
Fetch From cur_Depart into @CName,@CID
End
Close cur_Depart
Deallocate cur_Depart