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