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

作者：shell                   来源：CSDN
--列出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