常用SQL语句

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