1.查找触发器代码里是否包含指定的串
set nocount on
Create table #y (Trname varchar(50),txt text)
select name, iid = identity(int,1,1) into #x from SysObjects where xtype = 'TR'
declare @i int, @max int
declare @name varchar(50)
set @i = 1
select @max = max(iid) from #x
while @i <= @max
begin
select @name = name from #x where iid = @i
insert #y (txt)
exec('sp_helptext ' + @name)
update #y
set Trname=@name
where Trname is null
set @i = @i + 1
end
select * from #y where txt LIKE '%查询关键字%'
drop table #x
drop table #y
set nocount off
2.查找存储过程代码里是否包含指定的串
select name,text
from sysobjects o,syscomments s
where o.id=s.id
and text like '%查询关键字%'
and o.xtype='p'
3.根据表名生成自动生成INSERT语句存储过程
IF(OBJECT_ID('proc_insert')IS NOT null)
DROP PROC proc_insert
go
CREATE proc [dbo].[proc_insert] (@tablename varchar(256))
as
begin
set nocount on
--declare @tablename varchar(256)
--set @tablename = 'AD'
declare @sqlstr varchar(MAX)
declare @sqlstr1 varchar(MAX)
declare @sqlstr2 varchar(MAX)
select @sqlstr='SELECT ''INSERT INTO [dbo].['+@tablename+']'
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= '
VALUES ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+ '[' + name + ']' +',' from (select case
-- when a.xtype =173 then 'CASE WHEN '+a.name+' is null then ''NULL'' ELSE '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =36 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'convert(varchar(36),['+a.name +'])'+ '+'''''''''+' end'
when a.xtype =104 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(1),['+a.name +'])'+' end'
when a.xtype =175 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =61 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'convert(varchar(23),['+a.name +'],121)'+ '+'''''''''+' end'
when a.xtype =40 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(100),['+a.name +'],23)'+' end'
when a.xtype =106 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),['+a.name +'])'+' end'
when a.xtype =62 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(23),['+a.name +'],2)'+' end'
when a.xtype =56 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(11),['+a.name +'])'+' end'
when a.xtype =60 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(22),['+a.name +'])'+' end'
when a.xtype =239 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =108 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),['+a.name +'])'+' end'
when a.xtype =231 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =59 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(23),['+a.name +'],2)'+' end'
when a.xtype =58 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'convert(varchar(23),['+a.name +'],121)'+ '+'''''''''+' end'
when a.xtype =52 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(12),['+a.name +'])'+' end'
when a.xtype =122 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(22),['+a.name +'])'+' end'
when a.xtype =48 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(6),['+a.name +'])'+' end'
-- when a.xtype =165 then 'CASE WHEN '+a.name+' is null then ''NULL'' ELSE '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =167 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end'
ELSE '''NULL'''
end as col,a.colid,a.name
from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35
)t order by colid
select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')''
FROM [dbo].['+@tablename+']'
--print @sqlstr
exec( @sqlstr)
set nocount off
end
GO
4.根据表名生成自动生成SELECT语句存储过程
IF(OBJECT_ID('proc_select')IS NOT null)
DROP PROC proc_select
go
CREATE proc [dbo].[proc_select] (@tablename varchar(256))
as
begin
declare @sqlstr varchar(MAX)
select @sqlstr='SELECT '' SELECT ''+ '
SELECT @sqlstr=@sqlstr+CASE WHEN [pindex]=1 THEN col ELSE +'+'',''+'+col END
FROM (
select case
when a.xtype =36 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'convert(varchar(36),['+a.name +'])'+ '+'''''''''+' end + '' ['+a.name +']'''
when a.xtype =104 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(1),['+a.name +'])'+' end + '' ['+a.name +']'''
when a.xtype =175 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end + '' ['+a.name +']'''
when a.xtype =61 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'convert(varchar(23),['+a.name +'],121)'+ '+'''''''''+' end + '' ['+a.name +']'''
when a.xtype =40 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(100),['+a.name +'],23)'+' end + '' ['+a.name +']'''
when a.xtype =106 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),['+a.name +'])'+' end + '' ['+a.name +']'''
when a.xtype =62 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(23),['+a.name +'],2)'+' end + '' ['+a.name +']'''
when a.xtype =56 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(11),['+a.name +'])'+' end + '' ['+a.name +']'''
when a.xtype =60 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(22),['+a.name +'])'+' end + '' ['+a.name +']'''
when a.xtype =239 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end + '' ['+a.name +']'''
when a.xtype =108 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),['+a.name +'])'+' end + '' ['+a.name +']'''
when a.xtype =231 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end + '' ['+a.name +']'''
when a.xtype =59 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(23),['+a.name +'],2)'+' end + '' ['+a.name +']'''
when a.xtype =58 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'convert(varchar(23),['+a.name +'],121)'+ '+'''''''''+' end + '' ['+a.name +']'''
when a.xtype =52 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(12),['+a.name +'])'+' end + '' ['+a.name +']'''
when a.xtype =122 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(22),['+a.name +'])'+' end + '' ['+a.name +']'''
when a.xtype =48 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'convert(varchar(6),['+a.name +'])'+' end + '' ['+a.name +']'''
-- when a.xtype =165 then 'CASE WHEN '+a.name+' is null then ''NULL'' ELSE '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =167 then 'CASE WHEN ['+a.name+'] is null then ''NULL'' ELSE '+'''''''''+'+'replace(['+a.name+'],'''''''','''''''''''')' + '+'''''''''+' end + '' ['+a.name +']'''
ELSE '''NULL'''
end as col,a.colid,a.name,ROW_NUMBER() OVER(ORDER BY a.colid) [pindex]
from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35
)t
order by [pindex]
select @sqlstr=@sqlstr+'
FROM [dbo].['+@tablename+']'
print @sqlstr
end
GO