• 收藏几段SQL Server语句和存储过程


    收藏几段SQL  Server语句和存储过程

    -- ======================================================

    --列出SQL SERVER 所有表,字段名,主键,类型,长度,小数位数等信息

    --在查询分析器里运行即可,可以生成一个表,导出到EXCEL中

    -- ======================================================

    SELECT

           (case when a.colorder=1 then d.name else '' end)表名,

           a.colorder 字段序号,

           a.name 字段名,

           (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,

           (case when (SELECT count(*)

           FROM sysobjects

           WHERE (name in

                     (SELECT name

                    FROM sysindexes

                    WHERE (id = a.id) AND (indid in

                              (SELECT indid

                             FROM sysindexkeys

                             WHERE (id = a.id) AND (colid in

                                       (SELECT colid

                                      FROM syscolumns

                                      WHERE (id = a.id) AND (name = a.name))))))) AND

                  (xtype = 'PK'))>0 then '√' else '' end) 主键,

           b.name 类型,

           a.length 占用字节数,

           COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,

           isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,

           (case when a.isnullable=1 then '√'else '' end) 允许空,

           isnull(e.text,'') 默认值,

           isnull(g.[value],'') AS 字段说明   

    FROM  syscolumns  a left join systypes b

    on  a.xtype=b.xusertype

    inner join sysobjects d

    on a.id=d.id  and  d.xtype='U' and  d.name<>'dtproperties'

    left join syscomments e

    on a.cdefault=e.id

    left join sysproperties g

    on a.id=g.id AND a.colid = g.smallid 

    order by a.id,a.colorder

    -------------------------------------------------------------------------------------------------

    列出SQL SERVER 所有表、字段定义,类型,长度,一个值等信息

    并导出到Excel 中

    -- ======================================================

    -- Export all user tables definition and one sample value

    -- jan-13-2003,Dr.Zhang

    -- ======================================================

    在查询分析器里运行:

    SET ANSI_NULLS OFF

    GO

    SET NOCOUNT ON

    GO

    SET LANGUAGE 'Simplified Chinese'

    go

    DECLARE @tbl nvarchar(200),@fld nvarchar(200),@sql nvarchar(4000),@maxlen int,@sample nvarchar(40)

    SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t

    FROM  syscolumns  a,  systypes b,sysobjects d 

    WHERE  a.xtype=b.xusertype  and  a.id=d.id  and  d.xtype='U'

    DECLARE read_cursor CURSOR

    FOR SELECT TableName,FieldName FROM #t

    SELECT TOP 1 '_TableName                     ' TableName,

                'FieldName                      ' FieldName,'TypeName             ' TypeName,

                'Length' Length,'IS_NULL' IS_NULL,

                'MaxLenUsed' AS MaxLenUsed,'Sample Value          ' Sample,

                 'Comment   ' Comment INTO #tc FROM #t

    OPEN read_cursor

    FETCH NEXT FROM read_cursor INTO @tbl,@fld

    WHILE (@@fetch_status <> -1)  --- failes

    BEGIN

           IF (@@fetch_status <> -2) -- Missing

           BEGIN

                  SET @sql=N'SET @maxlen=(SELECT max(len(cast('+@fld+' as nvarchar))) FROM '+@tbl+')'

                  --PRINT @sql

                  EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT

                  --print @maxlen

                  SET @sql=N'SET @sample=(SELECT TOP 1 cast('+@fld+' as nvarchar) FROM '+@tbl+' WHERE len(cast('+@fld+' as nvarchar))='+convert(nvarchar(5),@maxlen)+')'

                  EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT

                  --for quickly  

                  --SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+

                         --@tbl+' order by 1 desc ))' 

                  PRINT @sql

                  print @sample

                  print @tbl

                  EXEC SP_EXECUTESQL @sql,N'@sample nvarchar(30) OUTPUT',@sample OUTPUT

                  INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,

                         convert(nchar(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld

           END

           FETCH NEXT FROM read_cursor INTO @tbl,@fld

    END

    CLOSE read_cursor

    DEALLOCATE read_cursor

    GO

    SET ANSI_NULLS ON

    GO

    SET NOCOUNT OFF

    GO

    select count(*)  from #t

    DROP TABLE #t

    GO

    select count(*)-1  from #tc

    select * into ##tx from #tc order by tablename

    DROP TABLE #tc

    --select * from ##tx

    declare @db nvarchar(60),@sql nvarchar(3000)

    set @db=db_name()

    --请修改用户名和口令 导出到Excel 中

    set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:\'+@db+'_exp.xls -w -C936 -Usa -Psa '''

    print @sql

    exec(@sql)

    GO

    DROP TABLE ##tx

    GO

    -- ======================================================

    --根据表中数据生成insert语句的存储过程

    --建立存储过程,执行 spGenInsertSQL 表名

    --感谢playyuer

    -- ======================================================

    CREATE   proc spGenInsertSQL (@tablename varchar(256))

    as

    begin

      declare @sql varchar(8000)

      declare @sqlValues varchar(8000)

      set @sql =' ('

      set @sqlValues = 'values (''+'

      select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'

        from

            (select case

                      when xtype in (48,52,56,59,60,62,104,106,108,122,127)                               

                           then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'

                      when xtype in (58,61)

                           then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'

                     when xtype in (167)

                           then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

                      when xtype in (231)

                           then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

                      when xtype in (175)

                           then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'

                      when xtype in (239)

                           then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'

                      else '''NULL'''

                    end as Cols,name

               from syscolumns 

              where id = object_id(@tablename)

            ) T

      set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename

      --print @sql

      exec (@sql)

    end

    GO

    -- ======================================================

    --根据表中数据生成insert语句的存储过程

    --建立存储过程,执行 proc_insert 表名

    --感谢Sky_blue

    -- ======================================================

    CREATE proc proc_insert (@tablename varchar(256))

    as

    begin

           set nocount on

           declare @sqlstr varchar(4000)

           declare @sqlstr1 varchar(4000)

           declare @sqlstr2 varchar(4000)

           select @sqlstr='select ''insert '+@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 =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 =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 and  a.xtype <>36

           )t order by colid

          

           select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename

    --  print @sqlstr

           exec( @sqlstr)

           set nocount off

    end

    GO

  • 相关阅读:
    BPC (9) SAP BI & BPC 安装 : 一个外行眼里的千奇百怪 (1)
    ESB (2) POCSofewareAG
    BPC (7) BPC Netweaver 7 和 microsoft 7 版本的差异
    ESB (3) POCOralce ESB
    厘清了xorg里的一些概念
    Top命令和Kill命令
    ubuntu中文英文环境切换
    /etc/passwd 文件内容详细解释
    [分享] Linux下用Anjuta写个Hello World 的C++程序竟如此简单!
    /proc目录
  • 原文地址:https://www.cnblogs.com/azhai/p/307061.html
Copyright © 2020-2023  润新知