收藏几段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

 

 

说明：本贴纯属收藏，目的在于大家交流，在此对作者表示感谢！